使用Biml脚本生成SSIS包

culuo4781

于 2020-07-25 20:45:26 发布

阅读量257

点赞数

原文链接：https://www.sqlshack.com/using-biml-scripts-to-generate-ssis-packages/

版权

In the previous article, Converting SSIS packages to Biml scripts, we explained how to convert existing SSIS packages into Biml scripts using Import Packages tool and we mentioned that this could be an efficient way to learn this markup language since it lets the user compare between the well-known SSIS objects found in the package and the correspondent elements in the generated scripts.

在上一篇文章“ 将SSIS包转换为Biml脚本”中，我们介绍了如何使用“导入包”工具将现有SSIS包转换为Biml脚本，并且我们提到这是学习此标记语言的有效方法，因为它使用户可以在软件包中找到的众所周知的SSIS对象以及生成的脚本中的对应元素。

In this article, we will explain how to generate SSIS packages using business intelligence markup language by providing a step-by-step guide where we will illustrate how to configure each object and how to use variables, expressions and parameters within the package.

在本文中，我们将通过提供分步指南来说明如何使用商业智能标记语言生成SSIS包，其中将说明如何配置每个对象以及如何在包中使用变量，表达式和参数。

所需的输出 (Desired output)

We will build an SSIS package that read a directory path from parameter, create a destination SQL table, loop over flat files (*.csv) within the directory and import them to the created table after adding two derived columns; one contains username (value stored within SSIS variable) and the other contains the current date and time. To do that, we have to add the following objects:

我们将构建一个SSIS包，该包将从参数中读取目录路径，创建目标SQL表，在目录内的平面文件（* .csv）上循环，并在添加两个派生列之后将它们导入到创建的表中；一个包含用户名（存储在SSIS变量中的值），另一个包含当前日期和时间。为此，我们必须添加以下对象：

Connection managers: 连接管理器：
1. Flat File Connection manager: where connection string must be dynamic to be able to read file paths enumerated by Foreach Loop container 平面文件连接管理器：其中的连接字符串必须是动态的，以便能够读取Foreach循环容器枚举的文件路径
2. OLE DB Connection manager: where destination table must be created OLE DB连接管理器：必须在其中创建目标表的位置
Tasks: 任务：
1. Execute SQL Task: To create destination SQL table using the following command:
  
  执行SQL任务：使用以下命令创建目标SQL表：
```
CREATE TABLE [dbo].[Person](
                  [BusinessEntityID] [int] NOT NULL,
                  [PersonType] [nchar](2) NOT NULL,
                  [NameStyle] [nvarchar](50) NOT NULL,
                  [Title] [nvarchar](8) NULL,
                  [FirstName] [nvarchar](50) NOT NULL,
                  [MiddleName] [nvarchar](50) NULL,
                  [LastName] [nvarchar](50) NOT NULL,
                  [Suffix] [nvarchar](10) NULL,
                  [EmailPromotion] [int] NOT NULL,
                  [ModifiedBy] [nvarchar](50) NOT NULL,
                  [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY]
```
2. Foreach Loop container: To loop over flat files within the source directoryForeach循环容器：循环遍历源目录中的平面文件
3. Data Flow Task: Placed within the Foreach Loop container and it contains the following components: 数据流任务：放置在Foreach循环容器中，它包含以下组件：
  1. Flat File Source: Read from the Flat File connection manager平面文件源：从平面文件连接管理器中读取
  2. Derived Column Transformation: Add two desired derived columns派生列转换：添加两个所需的派生列
  3. OLE DB Destination: The destination table is the created SQL table using the Execute SQL TaskOLE DB目标：目标表是使用执行SQL任务创建SQL表
Parameters: 参数：
1. SourceDirectory (Type = string, Required = True)SourceDirectory （类型=字符串，必填= True）
Variables: 变量：
1. DestinationTable (Type = string, Value = “[dbo].[Person]”)DestinationTable （类型=字符串，值=“ [dbo]。[人]”）
2. Username (Type = string, Value = “admin”)用户名 （类型=字符串，值=“管理员”）
3. SourceFlatFile (Type = string): This variable will be used by Foreach Loop container to enumerate flat filesSourceFlatFile （类型=字符串）：Foreach循环容器将使用此变量来枚举平面文件

编写BIML脚本 (Writing a BIML script)

In our previous article, we have noted that there are three parts within the script:

在上一篇文章中，我们注意到脚本中包含三个部分：

Connections: where connection managers are defined连接：定义连接管理器的位置
Packages: where all package with their tasks, components, variables and parameters are defined包：定义所有包及其任务，组件，变量和参数
FileFormats: where external file connections metadata is definedFileFormats ：定义了外部文件连接元数据的位置

In this section, we will explain how to add a Biml script to the solution, then we will build each part then we will generate an SSIS package from the whole script.

在本节中，我们将说明如何向解决方案中添加Biml脚本，然后构建每个部分，然后从整个脚本生成SSIS包。

添加新脚本 (Adding a new script)

To add a new script to the solution, in the menu strip go to “Extensions” > “BimlExpress” > “Add New Biml File” as shown in the image below:

要将新脚本添加到解决方案中，请在菜单栏中转到“扩展名”>“ BimlExpress”>“添加新Biml文件”，如下图所示：

Adding a new Biml script to the solution

Figure 1 – Add a new script to the solution

图1 –将新脚本添加到解决方案

A new script will be added within the Miscellaneous folder. Open the script and you will find the main XML element:

新脚本将添加到“杂项”文件夹中。打开脚本，您将找到主要的XML元素：

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

文件格式 (FileFormats)

This part is used to define the flat-file metadata to be used within the Flat File connection manager. Within this part, we have to define the flat file structure within a FlatFileFormat element where we have to specify different attributes such as Name, CodePage, TextQualifier, RowDelimiter and ColumnNamesInFirstRow. Then for each column, we have to add a Column element as shown below:

此部分用于定义将在平面文件连接管理器中使用的平面文件元数据。在这一部分中，我们必须在FlatFileFormat元素中定义平面文件结构，在其中必须指定不同的属性，例如Name，CodePage，TextQualifier，RowDelimiter和ColumnNamesInFirstRow。然后，对于每一列，我们必须添加一个Column元素，如下所示：

<FileFormats>
    <FlatFileFormat Name="Flat File Connection Manager" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
        <Columns>
            <Column Name="BusinessEntityID" Length="50" DataType="AnsiString" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="PersonType" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="NameStyle" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="Title" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="FirstName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="MiddleName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="LastName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="Suffix" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
            <Column Name="EmailPromotion" Length="50" DataType="String" Delimiter="CRLF" MaximumWidth="50" />
        </Columns>
    </FlatFileFormat>
</FileFormats>

连接数 (Connections)

All connection managers must be defined within the connections element. In this example, we need to add two elements within “Connections”:

必须在connections元素内定义所有连接管理器。在此示例中，我们需要在“连接”中添加两个元素：

Connection for the OLE DB Connection manager where two attributes must be defined; name and ConnectionString 连接的其中两个属性必须定义的OLE DB连接管理器; 名称和ConnectionString
FlatFileConnection for the Flat File connection manager where three attributes must be defined; name, FilePath and FileFormat (we have to select the FlatFileFormat name attribute defined in the section above) FlatFileConnection用于平面文件连接管理器，其中必须定义三个属性；名称，FilePath和FileFormat（我们必须选择在上一节中定义的FlatFileFormat名称属性）

The connections element will look like the following:

connections元素如下所示：

<Connections>
    <Connection Name="DestinationConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <FlatFileConnection Name="Flat File Connection Manager" FilePath="" FileFormat="Flat File Connection Manager" />
</Connections>

As you noted, we didn’t set the flat file ConnectionString expression in this part since it will be defined within the package part.

如您所述，我们没有在此部分中设置平面文件ConnectionString表达式，因为它将在包部分中定义。

配套 (Packages)

This is the main part where we have to define each package with every included object. First, we have to add a package element that contains the name and other attributes.

这是我们必须定义每个包含每个对象的包的主要部分。首先，我们必须添加一个包含名称和其他属性的package元素。

<Package Name="Package3" Language="None" ConstraintMode="LinearOnSuccess" ProtectionLevel="EncryptSensitiveWithUserKey"></Package>

Within the package element, we have to define variables, tasks, parameters and connections configuration (expressions) as the following:

在package元素内，我们必须定义变量，任务，参数和连接配置（表达式），如下所示：

变数 (Variables)

In this element, each variable must be defined using the “Variable” element as the following:

在此元素中，必须使用“ Variable”元素来定义每个变量，如下所示：

<Variables>
    <Variable Name="DestinationTable" DataType="String" IncludeInDebugDump="Exclude">[dbo].[Person]</Variable>
    <Variable Name="SourceFlatFile" DataType="String" IncludeInDebugDump="Exclude"></Variable>
    <Variable Name="Username" DataType="String" IncludeInDebugDump="Exclude">Admin</Variable>
</Variables>

参量 (Parameters)

In this element, each variable must be defined using “Parameter” element as the following:

在此元素中，必须使用“ Parameter”元素如下定义每个变量：

<Parameters><Parameter Name="SourceDirectory" DataType="String" IsRequired="true">F:\TextFiles</Parameter>
</Parameters>

连接数 (Connections)

Within this element, we have to add a Connection element where ConnectionName attribute is used to specify the related connection manager, then we can set the expressions within this element by adding an “Expression” element to define the ConnectionString expression (read the flat file path from “@[User::SourceFlatFile] variable) as the following:

在该元素内，我们必须添加一个Connection元素，其中ConnectionName属性用于指定相关的连接管理器，然后我们可以通过添加“ Expression”元素来定义ConnectionString表达式来设置该元素内的表达式（读取平面文件路径）来自“ @ [User :: SourceFlatFile]变量），如下所示：

<Connections>
    <Connection ConnectionName="Flat File Connection Manager">
        <Expressions>
            <Expression ExternalProperty="ConnectionString">@[User::SourceFlatFile]</Expression>
        </Expressions>
    </Connection>
</Connections>

任务 (Tasks)

The “Tasks” element is used to define tasks within the main control flow or containers (For Loop, Foreach Loop, Sequence containers).

“任务”元素用于在主控制流或容器（For循环，Foreach循环，序列容器）中定义任务。

First, we have to define the Execute SQL task that create the destination table:

首先，我们必须定义创建目标表的Execute SQL任务：

<ExecuteSQL Name="Execute SQL Task" ConnectionName="DestinationConnection">
    <DirectInput>CREATE TABLE [dbo].[Person](
        [BusinessEntityID] [int] NOT NULL,
        [PersonType] [nchar](2) NOT NULL,
        [NameStyle] [nvarchar](50) NOT NULL,
        [Title] [nvarchar](8) NULL,
        [FirstName] [nvarchar](50) NOT NULL,
        [MiddleName] [nvarchar](50) NULL,
        [LastName] [nvarchar](50) NOT NULL,
        [Suffix] [nvarchar](10) NULL,
        [EmailPromotion] [int] NOT NULL,
        [ModifiedBy] [nvarchar](50) NOT NULL,
        [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY]
  </DirectInput>
</ExecuteSQL>

Next, we should define the Foreach Loop container, set the enumerator type to “File enumerator”, and to evaluate the DirectoryPath property as expression (read from SourceDirectory parameter), also we have to map the enumerator output to SourceFlatFile variable.

接下来，我们应该定义Foreach循环容器，将枚举器类型设置为“ File enumerator”，并将DirectoryPath属性评估为表达式（从SourceDirectory参数读取），还必须将枚举器输出映射到SourceFlatFile变量。

<ForEachFileLoop Name="Foreach Loop Container" ConstraintMode="LinearOnCompletion" Folder="" FileSpecification="*.csv">
    <Expressions>
        <Expression ExternalProperty="Directory">@[$Package::SourceDirectory]</Expression>
    </Expressions>
    <VariableMappings>
        <VariableMapping Name="0" VariableName="User.SourceFlatFile" />
    </VariableMappings>
</ForEachFileLoop>

Finally, we should add another <Tasks></Tasks> element to define all tasks within the container. In this example we should add a DataFlow element where we add a FlatFileSource, DerivedColumns and OledbDestination as following:

最后，我们应该添加另一个<Tasks> </ Tasks>元素来定义容器中的所有任务。在此示例中，我们应该添加一个DataFlow元素，在其中添加FlatFileSource，DerivedColumns和OledbDestination，如下所示：

FlatFileSource must be linked to the Flat File connection manager
FlatFileSource必须链接到Flat File连接管理器
DerivedColumns must contain two DerivedColumn; one for the user name and the other for the current date and time
DerivedColumns必须包含两个DerivedColumn；一个用于用户名，另一个用于当前日期和时间
OleDbDestination must be linked to the OLE DB Connection manager, and the destination must read the table name from the DestinationTable variable
OleDbDestination必须链接到OLE DB连接管理器，并且目标必须从DestinationTable变量读取表名

The Biml code should look like the following:

Biml代码应如下所示：

<Tasks>
  <Dataflow Name="Data Flow Task">
    <Transformations>
      <FlatFileSource Name="Flat File Source" LocaleId="None" FileNameColumnName="" ConnectionName="Flat File Connection Manager" />
      <DerivedColumns Name="Derived Column">
        <Columns>
          <Column Name="ModifiedBy" DataType="String" Length="5">@[User::Username]</Column>
          <Column Name="ModifiedDate" DataType="DateTime">GETDATE()</Column>
        </Columns>
      </DerivedColumns>
      <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationConnection">
        <TableFromVariableOutput VariableName="User.DestinationTable" />
      </OleDbDestination>
    </Transformations>
  </Dataflow>
</Tasks>

最终Biml脚本 (Final Biml Script)

The final script should look like the following. We will not cover discussing each attribute and properties in the script element since their names and values are identical to those found within the SSIS editor.

最终脚本应如下所示。我们将不讨论脚本元素中的每个属性和属性，因为它们的名称和值与在SSIS编辑器中找到的名称和值相同。

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <FileFormats>
        <FlatFileFormat Name="Flat File Connection Manager" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="BusinessEntityID" Length="50" DataType="AnsiString" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="PersonType" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="NameStyle" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="Title" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="FirstName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="MiddleName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="LastName" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="Suffix" Length="50" DataType="String" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="EmailPromotion" Length="50" DataType="String" Delimiter="CRLF" MaximumWidth="50" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Connections>
        <Connection Name="DestinationConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
        <FlatFileConnection Name="Flat File Connection Manager" FilePath="" FileFormat="Flat File Connection Manager" />
    </Connections>
    <Packages>
        <Package Name="Package" Language="None" ConstraintMode="LinearOnSuccess" ProtectionLevel="EncryptSensitiveWithUserKey">
            <Variables>
                <Variable Name="DestinationTable" DataType="String" IncludeInDebugDump="Exclude">[dbo].[Person]</Variable>
                <Variable Name="SourceFlatFile" DataType="String" IncludeInDebugDump="Exclude"></Variable>
                <Variable Name="Username" DataType="String" IncludeInDebugDump="Exclude">Admin</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL Name="Execute SQL Task" ConnectionName="DestinationConnection">
                    <DirectInput>CREATE TABLE [dbo].[Person](
  [BusinessEntityID] [int] NOT NULL,
  [PersonType] [nchar](2) NOT NULL,
  [NameStyle] [nvarchar](50) NOT NULL,
  [Title] [nvarchar](8) NULL,
  [FirstName] [nvarchar](50) NOT NULL,
  [MiddleName] [nvarchar](50) NULL,
  [LastName] [nvarchar](50) NOT NULL,
  [Suffix] [nvarchar](10) NULL,
  [EmailPromotion] [int] NOT NULL,
  [ModifiedBy] [nvarchar](50) NOT NULL,
  [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY] </DirectInput>
                </ExecuteSQL>
                <ForEachFileLoop Name="Foreach Loop Container" ConstraintMode="LinearOnCompletion" Folder="F:\TextFiles" FileSpecification="*.csv">
                    <Expressions>
                        <Expression ExternalProperty="Directory">@[$Package::SourceDirectory]</Expression>
                    </Expressions>
                    <Tasks>
                        <Dataflow Name="Data Flow Task">
                            <Transformations>
                                <FlatFileSource Name="Flat File Source" LocaleId="None" FileNameColumnName="" ConnectionName="Flat File Connection Manager" />
                                <DerivedColumns Name="Derived Column">
                                    <Columns>
                                        <Column Name="ModifiedBy" DataType="String" Length="5">@[User::Username]</Column>
                                        <Column Name="ModifiedDate" DataType="DateTime">GETDATE()</Column>
                                    </Columns>
                                </DerivedColumns>
                                <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationConnection">
                                    <TableFromVariableOutput VariableName="User.DestinationTable" />
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                    <VariableMappings>
                        <VariableMapping Name="0" VariableName="User.SourceFlatFile" />
                    </VariableMappings>
                </ForEachFileLoop>
            </Tasks>
            <Connections>
                <Connection ConnectionName="Flat File Connection Manager">
                    <Expressions>
                        <Expression ExternalProperty="ConnectionString">@[User::SourceFlatFile]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Parameters>
                <Parameter Name="SourceDirectory" DataType="String" IsRequired="true">F:\TextFiles</Parameter>
            </Parameters>
        </Package>
    </Packages>
</Biml>

生成SSIS包 (Generating SSIS Package)

In order to generate an SSIS package, in the solution explorer tab, Right-Click on the Biml Script file and click on “Generate SSIS packages”:

为了生成SSIS包，在解决方案资源管理器选项卡中，右键单击Biml脚本文件，然后单击“生成SSIS包”：

Generating SSIS package from the Biml script

Figure 2 – Generate SSIS package from a script

图2 –从脚本生成SSIS包

A new package will be added to the solution. The following screenshots are for the package control flow, and data flow task where all defined objects are added.

新软件包将添加到解决方案中。以下屏幕快照用于包控制流和数据流任务，其中添加了所有已定义的对象。

Figure 3 – Control Flow screenshot

图3 –控制流屏幕截图

Figure 4 – Data Flow task screenshot

图4 –数据流任务屏幕截图

结论 (Conclusion)

In this article, we provided a step-by-step guide to create an SSIS package from a Biml script. We explained how to add some of the most used SSIS tasks such as Execute SQL Task, Foreach Loop Container, and Data Flow Task. In addition, we defined variables and parameters and evaluated some of the tasks and components properties as expressions.

在本文中，我们提供了从Biml脚本创建SSIS包的分步指南。我们介绍了如何添加一些最常用的SSIS任务，例如“执行SQL任务”，“ Foreach循环容器”和“数据流任务”。此外，我们定义了变量和参数，并将某些任务和组件属性评估为表达式。

目录 (Table of contents)

Getting started with Biml

Converting SSIS packages to Biml scripts

Using Biml scripts to generate SSIS packages

Extending Biml with C# scripts

Using external C# script files within Biml

Biml alternatives: Building SSIS packages programmatically using ManagedDTS

Biml alternatives: Building SSIS packages programmatically using EzAPI

使用Biml脚本生成SSIS包

Biml替代品：使用ManagedDTS以编程方式构建SSIS包

Biml替代品：使用EzAPI以编程方式构建SSIS包

翻译自: https://www.sqlshack.com/using-biml-scripts-to-generate-ssis-packages/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用Biml脚本生成SSIS包

In the previous article, Converting SSIS packages to Biml scripts, we explained how to convert existing SSIS packages into Biml scripts using Import Packages tool and we mentioned that this coul...
复制链接

扫一扫