Introduction

The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.

It is very popular because it is fast and easy to download.

This tool is installed by default with SQL Server. It is usually installed in the following path:

Drive: \\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

In this article we will learn how to:

1. Get the bcp arguments
2. Get the version
3. Export data from a SQL Server table to a file
4. Export data from a SQL Server query to a file
5. Run bcp using PowerShell
6. Run bcp on SSIS
7. Invoke a batch file in SSIS

Requirements

1. SQL Server installed
2. bcp installed
3. The Adventureworks database installed
4. SSIS Installed
5. SSDT installed

Get started

1. Get the bcp arguments

In the command line, write bcp. This will show you the possible arguments that can be used:

bcp /? Is the same than bcp. It shows you the possible parameters:

2. Get the version

You can get the version of the bcp using the -v argument:

Here you have a list of versions of bcp:

3. Export data from a SQL Server table to a file

To export data from a SQL Server table or view to a file, you can use the bcp command.

The following example exports the table person.person from the adventureworks database to a file named dimcustomer.bcp

bcp adventureworks.person.person out c:\sql\dimcustomer.bcp -c -T -S WIN-355CHQ0E524

The -c argument is used to perform operations using a character type. -T is used to connect using a Trusted connection (Windows Authentication). -S is used to specify the SQL Server name.

If everything is OK, the rows will be copied successfully:

You will receive the number of rows copied, the network packet size and the speed to copy the rows per second.

If everything is OK, the file will be created:

You can open the file and check the data:

4. Export data from a SQL Server query to a file

You can export data from a SQL Server Query to a file. To do this, you need to specify the query in quotes and use the out argument:

bcp adventureworks.person.person out c:\sql\dimcustomer.bcp -c -T -S WIN-355CHQ0E524

The result displayed is the following:

5. Run bcp using PowerShell

PowerShell is a powerful tool to automate tasks using scripts and the command line. You can also run bcp using PowerShell, which is included with Windows and it can be also installed on Linux and Mac.

In PowerShell create variables to store the database name, schema, table and output path:

Store the bcp command line with the parameters in another parameter:

Use the Invoke-Expression to call the variable with the bcp commands:

PowerShell will import the files in the powershell.txt file:

6. Run bcp on SSIS

It is not a common practice to run bcp on SSIS because SSIS contains tasks to import and export data. You can use the Data Flow task to create customized tasks or maybe use the Bulk Insert task to import data to SQL Server.

However, there are some scenarios where you can invoke bcp in SSIS. For example, if you already have some command lines in bcp and you just want to invoke them. Another case is when you are adept at the command line and you already have some batch files ready and you just want to invoke them.

Open the SSDT and create a New Project.

In projects select Integration Services Project:

Drag and drop the Execute Process Task to the design pane:

In executable, specify the path of the bcp file:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

In arguments specify the bcp arguments to export data from a SQL Server table to a text file named: ssis.bcp

adventureworks.person.person out c:\sql\ssis.bcp -c -T -S WIN-355CHQ0E524

Your SSIS tasks will look like this:

Right click the tasks and select Execute task:

If everything is OK, the task will be like this and a file named ssis.bcp will be created with the data exported:

7. Invoke a batch file in SSIS

If you are good using the command line, it is common to use SSIS to invoke a batch file that includes several commands and the bcp file. In this new example, we will export data from a SQL Server table to a file including the current date.

The following file named bcpimport.bat

The file will have the following command lines:

The first line will set the file name in a variable named var, including the current date with the extension bcp. For example, if today is November 6, the file name of the exported data will be batfileexport20170611.bcp.

To invoke the file in SSIS using the Execute Process task, use the following parameters:

In executable, we will invoke the cmd which is usually in the

c:\Windows\System32\cmd.exe

In arguments, we will use /c bcpimport.bat to invoke the bat file.

If everything is fine, file will be created successfully:

Conclusions

Bcp is a powerful tool to import and export data from SQL Server tables to files or vice versa. It is fast, it is also a simple tool that can be easily downloaded. It is possible to export T-SQL queries to a file using bcp.

SSIS is another alternative that can be used if you need more customized and sophisticated solutions.

We also learned to invoke bcp in PowerShell. To do that we stored parameters in PowerShell and then we used the Invoke-Expression cmdlet.

Finally, we learned how to invoke bcp in SSIS. As we explained, it is not a common practice to invoked bcp in SSIS, but it is possible to do it using the Execute Process Task. However, it is common to invoke .bat files in SSIS and they can contain calls to the bcp. We learned how to use the SSIS Execute Process task to invoke a .bat file.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023