Най-добрата практика на 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>

На този етап просто трябва да разберете как искате да координирате изпълнението на пакетите.

Можете да използвате Execute SQL Task в SSIS, за да копирате с помощта на това или да изпълните директно от SQL Agent. Това предполага, че схемата вече съществува в целевата база данни.

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 процесът (консумация на процесора, консумация на памет и цялостен изход).