Как инженер данных или разработчик бизнес-аналитики, вы часто можете оказаться в ситуации, когда вам нужно создать несколько пакетов SSIS, требующих много повторяющейся работы. Это может быть трудоемкой задачей, особенно при работе с большими базами данных или сложными преобразованиями. Однако есть решение этой проблемы – BimlScript.

Что такое BimlScript?

BimlScript – это расширение языка Biml, которое позволяет генерировать, управлять и манипулировать скриптами Biml с использованием скриптов на VB или C#. Он предоставляет инструмент автоматизации, который может считывать метаданные базы данных, выполнять циклы по объектам базы данных и заменять статические значения выражениями. С помощью BimlScript вы можете значительно сократить время и усилия, затрачиваемые на создание пакетов SSIS.

Автоматизация генерации пакетов с помощью BimlScript

Допустим, вам нужно перенести несколько таблиц из одной базы данных в другую, применяя к каждой таблице одни и те же преобразования. Используя BimlScript, вы можете автоматизировать этот процесс, следуя этим шагам:

1. Укажите язык скрипта (C# или VB) и импортируйте необходимые сборки.
2. Получите метаданные таблиц из исходной базы данных.
3. Выполните цикл по таблицам и сгенерируйте пакет для каждой таблицы.
4. Определите менеджеры подключений внутри скрипта.
5. Добавьте и настройте задачу потока данных в каждом пакете.

Настройка скрипта

Чтобы расширить Biml с помощью скрипта на C# или VB, вам нужно добавить блок директивы, чтобы указать язык, который вы хотите использовать, и импортировать необходимые сборки. Этот код должен быть добавлен вне основного элемента <Biml>. Вот пример:

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  ...
</Biml>

Получение метаданных таблиц

Чтобы получить метаданные таблиц из базы данных, вам нужно установить соединение и получить метаданные с помощью блоков управления. Вот пример:

<# var con = SchemaManager.CreateConnectionNode("SourceConnection", "Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"); #>
<# var metadata = con.GetDatabaseSchema(new List<string> { "Person" }, null, ImportOptions.ExcludeViews); #>

Определение менеджеров подключений

Поскольку все пакеты будут использовать одни и те же менеджеры подключений, вы можете определить их на уровне проекта. Это должно быть сделано вне элемента <Packages>. Вот пример:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="SourceOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <Connection Name="DestinationOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
  ...
</Biml>

Цикл по таблицам

Чтобы сгенерировать несколько пакетов, вам нужно реализовать логику цикла внутри элемента <Packages>. Это можно сделать с помощью цикла foreach по полученным таблицам. Вот пример:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    ...
  </Connections>
  <Packages>
    <# foreach (var table in metadata.TableNodes.Where(x => x.Schema.Name == "Person")) { #>
    <Package Name="Extract_<#=table.Name#>">
      ...
    </Package>
    <# } #>
  </Packages>
</Biml>

Добавление и настройка задачи потока данных

Для каждого пакета вам нужно добавить задачу потока данных, которая содержит необходимые преобразования. Это можно сделать, добавив соответствующий код Biml внутри элемента <Package>. Вот пример:

<Tasks>
  <Dataflow Name="Data Flow Task">
    <Transformations>
      <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection">
        <ExternalTableInput Table="[Person].[<#=table.Name#>]" />
      </OleDbSource>
      <DerivedColumns Name="Derived Column">
        <Columns>
          <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column>
        </Columns>
      </DerivedColumns>
      <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection">
        <ExternalTableOutput Table="[Person].[<#=table.Name#>]" />
      </OleDbDestination>
    </Transformations>
  </Dataflow>
</Tasks>

Генерация пакетов

После настройки кода BimlScript вы можете сгенерировать пакеты SSIS, щелкнув правой кнопкой мыши на файле скрипта Biml в обозревателе решений и выбрав “Сгенерировать пакеты SSIS”. Это развернет код Biml и сгенерирует соответствующие пакеты.

Автоматизируя процесс генерации пакетов с помощью BimlScript, вы можете сэкономить значительное количество времени и усилий. Это позволяет избежать ручного создания