The Bulk Insert Task in SSIS is a tricky thing. It often creates a lot of confusion and frustration before it is willing to insert any rows into the database. This post will explain how it works differently in SSIS and how to solve the problems that many developers encounter.
The Bulk Insert task was probably included in SSIS as a means of completing a toolset with ones that SQL devs are already familiar with. However, this is the one task in SSIS that operates in a different context. Let’s suppose that we have a dedicated SSIS server which receives files and needs to insert their data into another server which houses the SQL database.
You start by adding a Bulk Insert task to your package and fiddle with it locally until you can get it to execute. Then you deploy the package to a testing environment that looks like production (separate SSIS and db servers). You run the package and get this error:
[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot bulk load. The file “c:myfile.csv” does not exist.”.
[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot bulk load because the file “\SSIS_ServerImportmyfile.txt” could not be opened. Operating system error code 5(Access is denied.).”.
You see that the file exists and that the your SSIS proxy account has permissions to it. But just in case, you open up access to the share to everyone. Still, you get the same error. To understand what went wrong, let’s look at the new context with which we are executing: