ssis best practice to load N tables from Source to Target Server

I’ve become a fan of using biml to solve these sorts of problems.

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”

Assuming everything is lined up, you’d end up with 29 packages that have a single data flow task in them pulling from source to destination (based on an SSIS Variable).

<#@ 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>

At this point, you simply need to figure out how you want to coordinate the execution of the packages.