CODEWITHSUNDEEP
sql-serverssis
Egor4eg
Egor4eg

Reputation: 2708

SSIS: transaction during simultaneous moving of data

I implemented SSIS package which moves data from Sql Server database to another one. This package has set of Data Flow Tasks which copy data simultaneously in different tables. Each Data Flow Task contain OLE DB datasource and Sql Server destination.

Package worked fine until I decided to implement transaction. I found that it is not possible to just set TransactionOption to Supported on package level, because SSIS cannot handle transactions in multiple simultaneous processes. So, I decided to use this way: http://consultingblogs.emc.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx But now I have another problem. I have "Unable to bulk copy data. You may need to run this package as an administrator" errors. These errors occur in random places. For example if I ran package in the first time Data Flow Task named "Task A" can be executed correctly, but when I run package in the second time it can throw the error.

How do I can implement transaction in my case? (Changing of package in order to perform execution of Data Flow tasks sequentially is not an option)

Upvotes: 2

Views: 2024

Answers (1)

EckartH
EckartH

Reputation: 41

I got a recent error with our MS SQL Server 2008R2 and SSIS. Found the error:

[SQL Server Destination [16]] Error: Unable to bulk copy data. You may need to run this package as an administrator. [SSIS.Pipeline] Error: component "SQL Server Destination" (16) failed the pre-execute phase and returned error code 0xC0202071.

but could not solve it with running as Admin. The error only came with one step and I finally found out that I get rid of the error when I increased the timeout of the SQL Server Destination. Funny is that with the read of external ADO NET Source I get a proper error that helped me to see the timeout is the problem.

Upvotes: 4

Related Questions