Лучшая практика ssis для загрузки N таблиц с исходного на целевой сервер

Я стал поклонником использования biml для решения подобных проблем.

1. Загрузите и установите Помощник по BIDS.
2. Добавьте новый файл biml в существующий проект SSIS.
3. Отключите автоматическое исправление Visual Studio для XML-файлов. См. раздел Устранение проблем с копированием и вставкой BimlScript в Visual Studio.
4. Вставьте этот код в этот файл Bimlscript.biml
5. Исправьте исходные и конечные строки подключения (строки 8 и 9), чтобы они указывали на правильные серверы, а также измените тип поставщика, если SQLNCLI11.1 не подходит для вашей версии SQL Server.
6. Щелкните правой кнопкой мыши файл biml и выберите «Создать пакеты SSIS».

Предполагая, что все выстроено, вы получите 29 пакетов, в которых есть одна задача потока данных, извлекающая из источника в пункт назначения (на основе переменной SSIS).

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

На этом этапе вам просто нужно выяснить, как вы хотите координировать выполнение пакетов.

Вы можете использовать задачу «Выполнение SQL» в SSIS, чтобы скопировать ее или выполнить непосредственно из агента SQL. Это предполагает, что схема уже существует в целевой базе данных.

DECLARE @tblname as varchar(50);
DECLARE @tblschema as varchar(50);
DECLARE @srcdb as varchar(12);
DECLARE @destdb as varchar(12);
DECLARE @tablesql as nvarchar(255);

SET @srcdb = 'MYSRCDB';
SET @destdb = 'MYDESTDB';

DECLARE tbl_cursor CURSOR FOR
SELECT table_schema, table_name FROM information_schema.tables where table_type = 'BASE TABLE'

OPEN tbl_cursor

FETCH NEXT FROM tbl_cursor INTO @tblschema, @tblname
WHILE @@FETCH_STATUS = 0
BEGIN

SET @tablesql = N'SELECT * into '+@destdb+'.'+@tblschema+'.'+@tblname+' FROM '+@srcdb+'.'+@tblschema+'.'+@tblname;
EXECUTE sp_executesql @tablesql

FETCH NEXT FROM tbl_cursor INTO @tblschema, @tblname
END

CLOSE tbl_cursor
DEALLOCATE tbl_cursor

Есть много факторов, которые влияют на то, какой сценарий выбрать.

Но в основном:

Для небольших таблиц с относительно небольшим количеством строк вы можете поместить несколько источников/назначений в один поток данных.

Если у вас есть сложные ETL для источника/назначения, то лучше поместить их в отдельные задачи потока данных для ясности.

Если вам нужно определить последовательность выполнения, вы должны использовать несколько задач потока данных, поскольку вы не можете контролировать порядок выполнения для нескольких источников/назначений в задачах одного потока данных.

Всякий раз, когда вам нужен другой уровень изоляции транзакций или поведение, вы должны поместить их в отдельные потоки данных.

Всякий раз, когда вы не уверены в влиянии ETL на исходную систему, поместите их в отдельные потоки данных, поскольку это позволит вам легче оптимизировать порядок выполнения в будущем.

Если у вас есть большие таблицы, поместите их в отдельные задачи потока данных, так как это позволит оптимизировать размеры буфера для разных таблиц и оптимизировать процесс ETL по любой причине.

Итак, из вышесказанного, если у вас относительно небольшие таблицы и прямое сопоставление источника/назначения, то нет проблем с большим количеством источников/назначений в одном потоке данных.

В остальных случаях лучше или необходимо вынести их в отдельные потоки данных, так как это позволит вам оптимизировать процесс ETL со всех трех точек зрения:

Влияние нагрузки на исходные системы

Влияние нагрузки на целевые системы

Использование машины, на которой выполняется процесс ETL (потребление ЦП, потребление памяти и общий объем вывода).