Data factory pipeline execution fails on SqlParameter

Question

Since a couple weeks our data factory pipelines sometimes fail due to below error message. We are running for approx 1,5 year in production and never experienced this error. So it is really strange why this happens. Also it does not happen each run, sometimes not for days and sometimes multiple consecutive runs.

We have nested pipelines like below

Master
  Master load sources
	Load source 1
	Load source 2
	..
  Master load warehouse
	..
  ..

For each pipeline we use a script task executing a stored procedure with parameters to insert custom logging. This is the activity that fails, sometimes. I am wondering if changing the script task to a stored procedure task would solve anything but knowing that it does not fail each time makes it worth asking around. Error message:

Operation on target For each source query failed: Activity failed because an inner activity failed; Inner activity name: Insert logging Source Copy data, Error: The SqlParameter is already contained by another SqlParameterCollection.

Answer 1

Hi Jordi, Thank you for posting query in Microsoft Q&A Platform.

Above error may occur when we try to use same parameter multiple times in SQL command or stored procedure. Firstly, make sure your are using SQL parameter names exactly same as in DB and also make sure not using same parameter name multiple times in activity.

In your case, I suspect same stored procedure activity may getting called multiple times and sometimes ending up with this error. Inside Execute pipeline activities Wait for pipeline execution to complete option mark it as true and make sure to create run Stored procedure only after its previous execution completes. That way this error may not come. Hope this helps. Please let me know how it goes. If it does not help, kindly try with stored procedure activity and see how it behaves. Please let me know if any further questions.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

"The SqlParameter is already contained by another SqlParameterCollection."

I have this error too. In ADF script activity with SP call.

It looks like ADF bug.

Just because it happens sometimes, not always. **Failure type "**User configuration issue" cannot happen sporadically, can it?
Why ms do not admit it is bug ?
I can provide all details.
Even more. I've found workaround. But should wait to state it for sure, due to the fact it happens from time to time (!!!)