Export Multiple SQL Tables to Text Files Using SSIS, Biml, and BCP

By:   |   Updated: 2019-04-16   |   Comments (4)   |   Related: More > Import and Export


Problem

My company is starting a new project where we are designing and building an enterprise data warehouse. We have many SQL Server databases that are used by our various line of business applications. I have been asked to develop an ETL application that can extract data from our SQL Server databases and load the data into our new data warehouse.  My first step is to create SSIS packages that can extract data from SQL Server tables to text files.  However, I need to extract data from hundreds of tables on a periodic basis.  What is the most efficient way to get this done?

Solution

The requirement to extract data from hundreds of tables in SQL Server databases can easily be done using SSIS, Biml and BCP.  Biml provides the capability of automatically generating SSIS packages.  BCP is a utility that comes with SQL Server and is very efficient at exporting the data in a table to a text file.

In this tip I will walk through the steps to implement the solution which is made up of the following SSIS packages (all generated from Biml):

  • Create BCP format files (1 package per table to export)
  • Export table to text file using BCP (1 package per table to export)
  • Create BCP format files master package (executes all packages that create BCP format files)
  • Export tables to text files master package (executes all packages that export a table to a text file)

Prerequisites

Although Biml has been around for a number of years, you will need to download and install BimlExpress, which is a free Visual Studio Extension for editing and building Biml files in an SSIS project.  Go to the Varigence site to download BimlExpress.  You can install it by simply double-clicking on the downloaded file (bimlexpress.vsix).  After installing you will need to request a key from Varigence when you open SQL Server Data Tools (SSDT) and try to use it.  You will receive the key by email and you can enter it when prompted in SSDT.

If you are new to Biml or need a refresher, please visit the Biml Tutorial on MSSQLTips.com.  The tutorial will provide the details you need to follow along with this tip.

BCP

BCP is the tried and true command-line utility that copies data to or from SQL Server tables.  You might say that BCP is "old school" and you would be correct.  However, it performs well and we just need to specify a relatively simple command-line to create BCP format files and export tables to text files. BCP works best when you have a BCP format file that describes the table columns and the text file fields to be copied to or from. BCP gets installed with SQL Server. The BCP Documentation has links to download and install the Microsoft Command Line Utilities for SQL Server which includes BCP.

Rather than use BCP, you could use Biml to add a Data Flow with a SQL Server source and a Flat File destination. I choose the BCP approach because I think it’s simpler.

Creating Biml Files

After installing BimlExpress, you will see BimlExpress in the top-level menu of an SSIS project:

bimlexpress

To add a Biml file to an SSIS project, click BimlExpress and select Add New Biml file from the dropdown menu.  Biml files are added to the Miscellaneous folder in your SSIS project.  Here is what you will see in the Solution Explorer for this tip:

solution explorer

Biml Files Review

Unlike the typical SSIS tip where we focus on the SSIS Toolbox components added to the Control Flow and Data Flow of the SSIS package, in this case we will focus on the Biml files. The Biml files are where we put the C# (or VB.Net) code that "generates" the SSIS packages to implement our solution. I use C# code in this tip. The code is contained inside the <# and #> constructs.

This solution has five Biml files. The naming convention used indicates the dependencies (higher numbers depend on lower numbers).  The actual dependencies are based on the "template tier" at the top of each Biml file.

I will walk through the Biml files in the following sections.

Configuration

I start each Biml project with a configuration file which is used for defining values (aka parameters, properties, settings, etc.) that are referenced in the Biml files that follow.  The idea is to have one place where these values are defined so it is easy to make changes and regenerate the SSIS packages.

The following is the Configuration Biml file:

biml configuration file

The following are the main points for the Configuration file:

  • RootNode is the element that contains the generated Biml; everything in the generated Biml file is a child of RootNode. The RootNode.ObjectTag["TAGNAME"] stores a value that can be referenced in any other Biml files in the project.
  • The Biml code creates a folder hierarchy on the local hard drive to hold BCP format files and the text files output from the SQL Server tables.
  • The folders are created if they do not exist. The folders are: C:\ETL\FORMAT_FILES, C:\ETL\EXPORT_FILES, and C:\ETL\OUTPUT_FILES.

Connections

I have a connections file in each Biml project to isolate the details of the various connections used in the SSIS packages. The following is the Connections Biml file:

biml configuration file

The following are the main points for the Connections file:

  • I use the approach of building up the connection string one element at a time to (hopefully) avoid silly syntax errors and make it easy to see the parts of the connection string.
  • Use the syntax <#=variablename#> to inject the variable value into a Biml element property.
  • The CreateInProject property when true creates a project connection in the generated SSIS packages.  A project connection is automatically in every SSIS package in the project.

Create SSIS Packages

The CreateSSISPackages Biml file is where it gets interesting. The Biml file creates the following SSIS packages for each table to be exported to a text file:

  • CREATE_<TABLENAME>_BCP_FORMAT_FILE.dtsx
  • EXPORT_<TABLENAME>_TABLE.dtsx

The CREATE_<TABLENAME>_BCP_FORMAT_FILE.dtsx SSIS package creates the BCP format file which is used by the BCP utility to export the table to a text file. The EXPORT_<TABLENAME>_TABLE.dtsx SSIS package executes the BCP command that exports the table to the text file.

There are 68 tables in the AdventureWorks 2014 database that get exported.  Therefore, this Biml file generates:

  • 68 CREATE_<TABLENAME>_BCP_FORMAT_FILE.dtsx SSIS packages
  • 68 EXPORT_<TABLENAME>_TABLE.dtsx SSIS packages

Since this Biml file is a bit long, I will review the initial C# code and the part that creates the SSIS packages separately. The following is the initial C# code:

biml configuration file

The following are the main points of the initial C# code:

  • I get the values defined in the Configuration and Connections files and assign them to local variables.  The local variable names are much easier to work with when injecting the values into the Biml element properties.
  • There are many ways to get the list of tables to be exported to text files.  I chose the GetDatabaseSchema method which is a built-in method in Biml.  The specific method I chose allows you to retrieve the schema information for the list of schemas you supply.
  • The GetDatabaseSchema method returns a collection of schema information (covered below).

The next part of the CreateSSISPackages Biml file creates the SSIS packages as noted above.

biml configuration file

The following are the main points for the above section of the Biml file:

  • The <Packages> element is the container for the SSIS package(s) that we want to generate.
  • The GetDatabaseSchema method (from the preceding code block) returns a collection of TableNodes which contains the information we need about each table to be exported to a text file (e.g. schema name and table name).
  • The "foreach" iterates through the schema collection one table at a time.
  • The code immediately after the "foreach" assigns values to local variables that we need to create the BCP format files and the BCP command to export the tables to text files.
  • The <Package> element defines an SSIS package to be generated.
  • I use an ExecuteProcess task to execute the BCP commands.

Create Master SSIS Package

A master SSIS package is simply an SSIS package that executes one or more SSIS packages.  In our case, we want to be able to execute one SSIS package and have it execute every SSIS package that was created in the Create SSIS Packages step above. The result will be to export every table to a text file using BCP. By having the individual SSIS packages created in the previous step, we also have the ability to selectively execute any SSIS package to create a BCP format file or export a table to a text file.

The following is the CreateMasterSSISPackage Biml file:

biml configuration file

The following are the main points of the Biml file:

  • The <Packages> element is the container for the SSIS package(s) that we want to generate.
  • The first <Package> element defines an SSIS package that executes all of the CREATE_<TABLENAME>_BCP_FORMAT_FILE.dtsx SSIS packages defined in the Create SSIS Packages section.
  • The "foreach" iterates through the list of SSIS packages defined in the Create SSIS Packages section. Note the StartsWith("CREATE") gets the list of packages that create the BCP format files.
  • An <ExecutePackage> task is added for each SSIS package to be executed.
  • The second <Package> element defines an SSIS package that executes all of the EXPORT_<TABLENAME>_TABLE.dtsx SSIS packages defined in the Create SSIS Packages section.
  • The "foreach" iterates through the list of SSIS packages defined in the Create SSIS Packages section. Note the StartsWith("EXPORT") gets the list of packages that export the SQL Server tables to text files.
  • An <ExecutePackage> task is added for each SSIS package to be executed.

Generate Debug Output

This last Biml file allows us to see the entire Biml document that gets created when you generate the SSIS packages. Based on my configuration settings, the file ExportSQLTablesToTextFilesUsingSSISBimlAndBCP.txt gets created in the C:\ETL\OUTPUT_FILES folder.

biml configuration file

Generate SSIS Packages

Follow these steps to generate the SSIS packages:

  1. Select all Biml files in the Miscellaneous folder in the Solution Explorer.
  2. Note that due to the dependencies, you have to select all Biml files that have lower numbers that the Biml file you want to check for errors or generate SSIS packages.
  3. Right-click on the selections and select Check Biml for Errors. If there are any errors you will likely have to select each file and check for errors in order to locate the error(s). Because of the dependencies between the Biml files, select the first file and check for errors; select the first two files and check for errors; select the first three file, etc.
  4. When there are no errors, right-click on the selections and select Generate SSIS Packages. You will see all of the generated packages in your SSIS project.

Execute SSIS Packages

After successfully generating the SSIS packages, run the following SSIS packages in this order:

  1. MASTER_CREATE_BCP_FORMAT_FILES.dtsx
  2. MASTER_EXPORT_TABLES.dtsx

Note that the BCP format files must be created before you can export a table to a text file.

You can also run any individual CREATE_<TABLENAME>_BCP_FORMAT_FILE.dtsx SSIS package or EXPORT_<TABLENAME>_TABLE.dtsx SSIS package.

Conclusion

This tip demonstrates the power of Biml and the ability to generate SSIS packages rather than creating them one-at-a-time. Imagine if you had to add a task to the generated SSIS packages. You just go back to the Biml file, add the task, and generate the SSIS packages again. Note that if you attempt to generate an SSIS package that already exists in the SSIS project, you will get prompted to confirm that you want to overwrite the existing package.

Keep in mind that if you make changes to a generated SSIS package and you generate the package again, your changes will be overwritten.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2019-04-16

Comments For This Article




Tuesday, April 16, 2019 - 4:38:16 PM - Raymond Barley Back To Top (79579)

Question from Greg Staples - for my specific scenario, executing an SSIS package as part of an ETL process is a good solution. Creating the SSIS package with Biml saves alot of time.

For your scenario, it's a perfectly acceptable approach. However, I would prefer a solution that doesn't rely on xp_cmdshell being enabled as this isn't a security best practice. One way to change this would be to have your stored procedure execute an SSIS package. I did this tip on how to do it: https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

Tuesday, April 16, 2019 - 4:27:01 PM - Raymond Barley Back To Top (79578)

Just to clarify, a BCP format file will be generated for every table that you want to export to a text file. 

I usually put all of the BCP format files in a different folder than where the exported text files go.

Tuesday, April 16, 2019 - 1:09:10 PM - Greg Staples Back To Top (79577)

Dear Ray,

RE: Export Multiple SQL Tables to Text Files Using SSIS, Biml, and BCP

Thanks for publishing this article.

This article caught my eye as I routinely use BCP and want to modernize my approach. Is the approach in this article my best way forward?

Here's a code snippet that I build into a stored procedure that end-users call from a Microsoft Dynamics pushbutton.  It works slick but more and more I find that recently trained SQL DBAs just don't like things being done this way.  It does require some security tweaks too.  I do the database joins and filters in the view so that I don't need to specify all that in the SP.

set @filename = 'GLARHeader.csv'
SET @bcpCommand ='bcp "SELECT  *  from [xglarview] "  queryout'
SET @OutputFile = @filename
SET @bcpCommand = @bcpCommand + @FilePath + @OutputFile + ' -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @bcpCommand

Again, your recommendation for a "modern" approach is appreciated.

Thank you,

Greg Staples

Tuesday, April 16, 2019 - 9:32:55 AM - jmoden Back To Top (79572)

Thanks for the article.

If I'm reading this correctly, because you have hundreds of tables that you're doing this for, you're going to end up with hundreds of BCP format files and hundreds of SSIS pages... is that correct?














get free sql tips
agree to terms