when executing my data flow package that contains only one source and one destination OLE db source -> SQL server destination the following errors occurs in my output Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14. Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion. Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071. i've checked the structure of my source and destination table but nothing seems to be wrong if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into? If not, it won't work. Use OLE DB Destination instead. -Jamie

I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).

Hi, I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance)that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution? Dave

Hi guys, I've mentioned some pre-requisites to getting SQL Server Destination working here: Destination Adapter Comparison(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx) which may or may not be useful. I hope it is. -Jamie

I just encountered this same issue which brought me to this dscussion thread...and on to the link that Jamie gave which discussed "Ole Db Destination" vs "SQL Server Destination" which gave me an idea. My scenario is a simple loading into an OLE DB ConnectionManager using a SQL Server Destination. All worked fine until I changed the InitCatalog property of the "OLE DB ConnectionManager" to be computed (to the exact same value that it had been hard-coded to): in the Expressions property of the "OLE DB ConnectionManager", I set the "InitCatalog" Property expression to a variable whose value was (for now) the same value as the hard-coded InitCatalog was. That change introduced the error. I tried setting "ValidateExternalMetaData" False on my SQL Server Destination but same result. Changing from a SQL Server Destination to an OLE DB Destination solved the problem. I realize this is an old thread so probably won't help the original posters, but may help someone else. I couldn't find anything saying that SQL Server Destination won't work with an OLE DB ConnectionManager whose ConnectionString is computed, but seems to be similar limitation as having to be on same machine. Anyone know?

Anyone that solved this with out changing to oledb. We have a ssis-package that tries to insert into a database on the same machine, but get failed the pre-execute phase and returned error code 0xC0202071 Unable to prepare the SSIS bulk insert for data insertion It works great on our testservers, but not in production. Regards

Is there any solution to this issue? I'm not talking about a work around - switching to an OLE DB Destination. I'm talking about a real solutionfor solving this problem. I am running a package using a SQL Server Destination and the first time, the package ran fine. The second time it failed with this error: "Unable to prepare the SSIS bulk insert for data insertion". I am running the package on the same server that the destination DB objects reside on. I find it strange that MS documentation will list the error message, but give no indication as to what it means. Again, has anyone found a true aolution to this problem, and not just a work around?

With all due respect for Jamie's experience with SSIS, what he as outlined is a work around and not a solution. Why is his post marked as being the answer to the problem when it is not a solution?

This is a permission issue, ifu run the package on the vista, close UAC, or run as administrator

So i just ran accross this interesting scenario:I had been running my SSIS package all day, on different types of data. I was also doing some manual queries on the database to verify the integrity of the data. All of a sudden, the next test run i did failed miserably with the error that spawned this thread, 0xC0202071. I ran my previous tests, and they were all failling now too.... in a fit of panic, i figured, maybe a restart will fix things.... so i began shutting down my instances of VS and SQL server... as i was closing SQL server, it informed me that i had open transactions. I accepted the dialog, which commited hte transactions. This made me go, hmmmmm.... lets try it again now with the transactions closed.....VOILA! So, make sure you don't have any open transactions pending.... and then try it again. Hope this helps others out there.

So i just ran accross this interesting scenario:I had been running my SSIS package all day, on different types of data. I was also doing some manual queries on the database to verify the integrity of the data. All of a sudden, the next test run i did failed miserably with the error that spawned this thread, 0xC0202071. I ran my previous tests, and they were all failling now too.... in a fit of panic, i figured, maybe a restart will fix things.... so i began shutting down my instances of VS and SQL server... as i was closing SQL server, it informed me that i had open transactions. I accepted the dialog, which commited hte transactions. This made me go, hmmmmm.... lets try it again now with the transactions closed.....VOILA! So, make sure you don't have any open transactions pending.... and then try it again. Hope this helps others out there!

So i just ran accross this interesting scenario:I had been running my SSIS package all day, on different types of data. I was also doing some manual queries on the database to verify the integrity of the data. All of a sudden, the next test run i did failed miserably with the error that spawned this thread, 0xC0202071. I ran my previous tests, and they were all failling now too.... in a fit of panic, i figured, maybe a restart will fix things.... so i began shutting down my instances of VS and SQL server... as i was closing SQL server, it informed me that i had open transactions. I accepted the dialog, which commited hte transactions. This made me go, hmmmmm.... lets try it again now with the transactions closed.....VOILA! So, make sure you don't have any open transactions pending.... and then try it again. Hope this helps others out there

Just in case someone else reads this thread: I was able to do a quick fix (work around) by changing from SQL Server authentication to Windows Authentication. Of course, this was just a quick fix to be able to run the job in the designer as the compiled version still runs fine using SQL server auth. Also, the designer is on the same machine as the database and my account is an admin on the box.

Hi, I found the same as West0. When in Visa if I launch BIDs as an administrator I don't get the error. If I launch BIDs without first logging on as an administrator I get the error; - 0x80019002. A side effect of this solution / work around is that the paths had to be absolute as apposed to being relative. I hope this helps someone. Kind Regards, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

The above UAC suggestion and running BIDS under elevated privileges did the trick for me. However, I also regard this a workaround - not a solution. Microsoft doesn't seem to work on SSIS anymore. I have filed dozens of bugs and suggestions at Connect two years ago, all of them received a [WON'T FIX]. SSIS is so buggy, it's not worth the time working with it.

I ran into this issue too. Quick fix in Bids... ran Bids as Administrator. Had configured my data source to use Windows Authentication. Worked grand after running Bids as administrator.

thanks Andy...it rocks !!!

Well, a deep sigh: I have a case with a client where giving a user the Create Global Objects permission does not resolve this issue unless him/I run the package as an admin in BIDS. Interestingly, the same package runs on another clone (the SQL Server Instance in use is local). Arthur My Blog

Run as administrator is the right answer. Thanks.