Bcp Tool to Test File Upload to Database

Introduction

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

Information technology is very pop 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\Customer SDK\ODBC\130\Tools\Binn\bcp.exe

In this article we will larn how to:

1. Get the bcp arguments
2. Get the version
3. Export data from a SQL Server table to a file
4. Consign 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

Go started

1. Become the bcp arguments

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

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

2. Get the version

Y'all tin get the version of the bcp using the -v argument:

Hither yous have a list of versions of bcp:

bcp version	SQL Server
fourteen.0.Ten.X	SQL Server 2017
thirteen.0.X.10	SQL Server 2016
12.0.10.X	SQL Server 2014
11.0.10.10	SQL Server 2012
10.0.X.X	SQL Server 2008
9.0.X.X	SQL Server 2005
eight.0.X.X	SQL Server 2000
7.0.X.X	SQL Server 7

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

To export data from a SQL Server tabular array or view to a file, you tin can use the bcp control.

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

bcp adventureworks.person.person out c:\sql\dimcustomer.bcp -c -T -Due south 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 proper noun.

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

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

If everything is OK, the file volition be created:

You can open the file and cheque the information:

4. Consign information from a SQL Server query to a file

You tin can export data from a SQL Server Query to a file. To do this, yous 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 upshot displayed is the following:

five. 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 tin be besides installed on Linux and Mac.

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

$ db = "adventureworks" $ schema = "person" $ table = "person" $ path = "C:\sql\powershell.txt"

Store the bcp command line with the parameters in some other parameter:

$ psCommand = "bcp $($db).$($schema).$($table) out $path -T -c"

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

Invoke - Expression $ psCommand

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

6. Run bcp on SSIS

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

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

Open the SSDT and create a New Projection.

In projects select Integration Services Projection:

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\Customer SDK\ODBC\130\Tools\Binn\bcp.exe

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

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

Your SSIS tasks will await like this:

Right click the tasks and select Execute chore:

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

seven. Invoke a batch file in SSIS

If you lot are adept using the command line, information technology is common to use SSIS to invoke a batch file that includes several commands and the bcp file. In this new example, we volition consign data from a SQL Server table to a file including the electric current date.

The following file named bcpimport.bat

The file will have the post-obit command lines:

set var = consign % appointment : ~ - iv , 4 % % date : ~ - 7 , two % % appointment : ~ - 10 , 2 % . bcp bcp adventureworks . person . person out c : \ sql \ batfile % var % . bcp - c - T - Southward WIN - 355CHQ0E524

The first line will ready the file name in a variable named var, including the current engagement with the extension bcp. For example, if today is Nov 6, the file proper noun of the exported information will be batfileexport20170611.bcp.

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

In executable, nosotros volition invoke the cmd which is commonly in the

c:\Windows\System32\cmd.exe

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

If everything is fine, file will exist 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 likewise 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 demand more than customized and sophisticated solutions.

We as well learned to invoke bcp in PowerShell. To practice that we stored parameters in PowerShell and and so we used the Invoke-Expression cmdlet.

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

• Author
• Recent Posts

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 thirteen years of experience working with dissimilar databases.

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

He besides helps with translating SQLShack articles to Castilian

View all posts by Daniel Calbimonte

martingolould.blogspot.com

Source: https://www.sqlshack.com/introduction-bcp-utility-bulk-copy-program-sql-server/

Share this post