The Bulk Copy Program Utility (BCP) is a very nifty little command line tool that allows us to bulk copy SQL Server data from or to a data file in a user specified format. The BCP is very useful when you need to copy large amounts of data into a data file for use in other programs or if you need to back up your data in a specified format for a third party. But the BCP is also very useful when you need to copy large amounts of data from one SQL Server table to another table. There are certain cases where using the BCP in SSIS may be the better choice over a Data Flow Task if no transformations are needed.

To use the BCP from SSIS we’re going to need 2 Execute Process Tasks (EPT) in our SSIS package: One Execute Process Task to pull the data from our table into a file and another one to pull the data from the file into our second table. And for the purposes of this little post, I’ve put it in a Sequence Container.

Then in our first EPT, we need to set up a couple different properties. The first is the Executable property. This tells our EPT which process to execute. In this case, it’s the Bulk Copy Program executable, bcp.exe, duh . If you’re not running SQL Server 2008, obviously the BCP isn’t going to be located under the 100 folder.

Secondly, we’ll need to specify our arguments. Now there are many different switches you can use, such as –e to generate an error file, –T to use integrated security when connecting to SQL Server, or –b to specify the rows per batch, but you can check out this article on MSDN to learn about all the different switches available. I would highly recommend reading through the article to learn about the different switches available. For our example, we’re going to use a few different switches to specify things like an error file (-e), using the native data types of the data (-N), and the server (-S).

Our first argument is the fully qualified database.schema.table which we would like to pull data from. In this case, it’s the table PaidVins2 in my CashForClunkersDW database. And since we are extracting data from a table, we will use out for the second argument of the BCP command. The third argument of the command will be the location of the BCP file.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp”

So far our BCP command should look like what you see above. But we still need to add a few switches before this will work. We at least need to specify the format of the BCP file using –N, which will create the BCP file with the data types native to our table. We could use –x to specify an xml formatted file or –c to specify character data types for all fields.

We also need to use the –T switch to let BCP know it’s a trusted connection. In place of –T we could use –U and –P to specify a username and password.

Next we’ll use the –S to specify the server and –e to create an output file for any errors that could occur.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins2errors.txt”

Enter your BCP command in the Arguments property of the Execute Process Task. If you execute the task, you should see the .bcp file created in the directory you specified. If you used the character (-c) format and specified the file as .txt (and the file is not too big!), you should be able to open it in a text editor.

The last thing we need to do is add an additional Execute Process Task to copy the .bcp file we just created into our destination table and connect the two with a precedence constraint. In my case, the table is PaidVins3. But instead of using the out switch, we’ll us in.

CashForClunkersDW.dbo.PaidVins3 in “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins3errors.txt”

And we’re done!

Now you can BCP large amount of data quickly from SSIS.