question

nikz2246 avatar image
0 Votes"
nikz2246 asked Monalv-msft edited

How to close datareader for ADO.NET source

Hi

I have created a package for data migration. My package use both execute SQL Task and Data flow task.
First data flow task(using ADO.NET source) works smoothly, when package flow reach second data flow task (using ADO.NET source), I get error

"There is already an open DataReader associated with this Command which must be closed first."

[SSIS.Pipeline] Error: "ADO NET Source" failed validation and returned validation status "VS_ISBROKEN".

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

Hi @nikz22,

You need to set connection property RetainSameConnection value as False.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @nikz2246 ,

First data flow task(using ADO.NET source) works smoothly, when package flow reach second data flow task (using ADO.NET source), I get error
"There is already an open DataReader associated with this Command which must be closed first."

Please create 2 ADO.NET connection managers in SSIS package.
First data flow task(using ADO.NET source) can use ADO.NET connection manager1, and second data flow task(using ADO.NET source) can use ADO.NET connection manager2.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is no need to mushroom connections, i.e. one connection per DFT. 👎

0 Votes 0 ·
nikz2246 avatar image
0 Votes"
nikz2246 answered

@ YitzhakKhabinsky-0887

Thanks! I chnaged the property to false and my solution is working perfectly fine.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.