SSIS Copy all tables

This could have also been titled, "holy crap biml is powerful" but that wouldn't do much for SEO. Instead, this post demonstrates how easy, how trivial it is to generate an SSIS package for every table in your database and copy all the data to a new server. Think about that. How often do you have a need to refresh N tables in a development environment from production. No, you can't do a restore because there's work in progress there, people just need some subset of data refreshed. This is yet another hammer for you to solve that problem with.

I have been working with Biml for a few months now but I haven't had a use case for pulling the scripting aspect into it. Today was that day, courtesy of this SO question: ssis best practice to load N tables from Source to Target Server

The following bimlscript will generate an SSIS package for every table in the source database and write all the source data to the matching table in the destination. For my example, I scripted out the Adventureworks2012DW into a new copy and ran the following biml.

What's it doing?

I create two connection strings to point to my source and destination. As noted over on StackOverflow, you will need to change this to your source and destination servers as well as verify the Provider matches your version.

I then have an query that pulls in all the tables and their schema, skipping any MS shipped ones and sysdiagrams. That query gets executed and dumped into a DataTable. That's all the setup really.

It's standard biml, create the Connections collection, define our Packages collection and then begin iterating through our DataTable.

Each Package will have 3 Variables: SchemaName, TableName and QualifiedTableSchema. The first two are set by values from our DataTable, the third is an expression built using the first two.

We add a Task collection and inside of it, a Data Flow with an OLE DB Source routed to an OLE DB Destination. I drive both of these by our Variable, QualifiedTableName.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<!--
<#
    string connectionStringSource = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";
    string connectionStringDestination = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012_DEST;Integrated Security=SSPI;Provider=SQLNCLI11.1";

    string SrcTableQuery =     @"
SELECT
    SCHEMA_NAME(t.schema_id) AS schemaName
,   T.name AS tableName
FROM
    sys.tables AS T
WHERE
    T.is_ms_shipped = 0
    AND T.name <> 'sysdiagrams';
";

    DataTable dt = null;
    dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery);
#>    
-->
    <Connections>
        <OleDbConnection
            Name="SRC"
            CreateInProject="false"
            ConnectionString="<#=connectionStringSource#>"
            RetainSameConnection="false">
        </OleDbConnection>
        <OleDbConnection
            Name="DST"
            CreateInProject="false"
            ConnectionString="<#=connectionStringDestination#>"
            RetainSameConnection="false">
        </OleDbConnection>
    </Connections>

    <Packages>
        <# foreach (DataRow dr in dt.Rows) { #>
            <Package ConstraintMode="Linear"
                Name="<#=dr[1].ToString()#>"

            >
            <Variables>
                <Variable Name="SchemaName" DataType="String"><#=dr[0].ToString()#></Variable>
                <Variable Name="TableName" DataType="String"><#=dr[1].ToString()#></Variable>
                <Variable Name="QualifiedTableSchema"
                          DataType="String"
                          EvaluateAsExpression="true">"[" +  @[User::SchemaName] + "].[" +  @[User::TableName] + "]"</Variable>
            </Variables>
            <Tasks>
                <Dataflow
                    Name="DFT"
                >
                    <Transformations>
                        <OleDbSource
                            Name="OLE_SRC <#=dr[0].ToString()#>_<#=dr[1].ToString()#>"
                            ConnectionName="SRC"
                        >
                            <TableFromVariableInput VariableName="User.QualifiedTableSchema"/>
                        </OleDbSource>
                        <OleDbDestination
                            Name="OLE_DST <#=dr[0].ToString()#>_<#=dr[1].ToString()#>"
                            ConnectionName="DST"
                            KeepIdentity="true"
                            TableLock="true"
                            UseFastLoadIfAvailable="true"
                            KeepNulls="true"
                            >
                            <TableFromVariableOutput VariableName="User.QualifiedTableSchema" />                        
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>

            </Tasks>
            </Package>
        <# } #>
    </Packages>
</Biml>

Usage

1. Download and install BIDS Helper
2. Add a new biml file to an existing SSIS project
3. Disable Visual Studio's auto-fix for XML files. See Overcoming BimlScript Copy and Paste Issues in Visual Studio
4. Paste this code into that Bimlscript.biml file
5. Fix your source and destination connection strings (lines 8 & 9) to point to the correct servers as well as change the Provider type if SQLNCLI11.1 is not correct for your version of SQL Server
6. Right-Click on the biml file and select "Generate SSIS Packages"