本文共 9676 字,大约阅读时间需要 32 分钟。
ssis 列转换
In our previous article, , we have explained what Biml is, what are the related tools and resources and how to get started with this technology. In this article, we will explain how to generate scripts from existing SSIS packages by showing all related options. Then, we will analyze the generated script and identify how each object is mentioned in the script.
在上一篇文章“ ,我们解释了Biml是什么,相关的工具和资源以及如何使用该技术。 在本文中,我们将通过显示所有相关选项来说明如何从现有的SSIS包中生成脚本。 然后,我们将分析生成的脚本并确定在脚本中如何提及每个对象。
To run our experiment, we will first build manually an SSIS package that creates a SQL table, read data from a flat-file – where the path is entered as a parameter – and insert it into the created table after adding two derived columns that contain the current date-time and a text value from an SSIS variable.
为了进行实验,我们将首先手动构建一个SSIS包,该包创建一个SQL表,从平面文件中读取数据(在该文件中,路径作为参数输入),并在添加两个包含以下内容的派生列之后将其插入到创建的表中:当前日期时间和SSIS变量的文本值。
The SSIS package should contain the following objects:
SSIS程序包应包含以下对象:
Execute SQL Task (Execute the command below):
执行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]
In order to convert an SSIS package into a business intelligence markup language script, you should go to the “Solution Explorer” tab, click on the package and press on “Convert SSIS package to BIML” as shown in the image below (note that BimlExpress should be installed):
要将SSIS包转换为商业智能标记语言脚本,应转到“解决方案资源管理器”选项卡,单击该包,然后按“将SSIS包转换为BIML”,如下图所示(请注意BimlExpress应该安装):
After you click on this button, the following form is opened:
单击此按钮后,将打开以下表单:
As you can see, there are many options that we should specify in this form:
如您所见,我们应该以这种形式指定许多选项:
In this example, we will not select any of the “SSIS Properties to include” and “Merging Into Project” options, since we are looking to generate a script of a single package object only, and we will leave “Auto Detect SSIS Version” option selected. After finishing configuration, we must click on the “Import” button which will detect the main objects included in this package as shown in the following image:
在此示例中,我们将不会选择任何“要包括的SSIS属性”和“合并到项目”选项,因为我们希望仅生成单个包对象的脚本,因此将保留“自动检测SSIS版本”选择的选项。 完成配置后,我们必须单击“导入”按钮,该按钮将检测此软件包中包含的主要对象,如下图所示:
We can reconfigure the import task and click on “Reimport”, or we can select the assets that we need to include within the script from the “Imported Assets” tree view and click on “Add To Project” button to generate the Biml script and add it within the current solution.
我们可以重新配置导入任务,然后单击“重新导入”,或者我们可以从“导入的资产”树视图中选择需要包含在脚本中的资产,然后单击“添加到项目”按钮以生成Biml脚本和将其添加到当前解决方案中。
In the following section, we will try to analyze the generated Biml script and to illustrate all the objects mentioned, if we open “BimlScript1.biml” file we can see the following script:
在下一节中,我们将尝试分析生成的Biml脚本并说明所提到的所有对象,如果打开“ BimlScript1.biml”文件,则可以看到以下脚本:
[dbo].[Person] Admin 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] @[User::Username] GETDATE() @[$Package::FlatFilePath] F:\SourceFile.txt
We can note that there are three main nodes within the scripts:
我们可以注意到脚本中有三个主要节点:
The following table contains the XML tag related for each object:
下表包含与每个对象相关的XML标记:
Object Type | XML Tag |
OLE DB Connection manager | <Connection /> |
Flat File Connection manager | <FlatFileConnection /> |
Execute SQL Task | <ExecuteSQL></ExecuteSQL> |
Data Flow Task | <DataFlow></DataFlow> |
Flat File Source | <FlatFileSource></FlatFileSource> |
Derived Column Transformation | <DerivedColumns></DerivedColumns> |
OLE DB Destination | <OleDbDestination></OleDbDestination> |
Variable | <Variable></Variable> |
Parameter | <Parameter></Parameter> |
对象类型 | XML标签 |
OLE DB连接管理器 | <连接/> |
平面文件连接管理器 | <FlatFileConnection /> |
执行SQL任务 | <ExecuteSQL> </ ExecuteSQL> |
数据流任务 | <DataFlow> </ DataFlow> |
平面文件源 | <FlatFileSource> </ FlatFileSource> |
派生列转换 | <DerivedColumns> </ DerivedColumns> |
OLE DB目标 | <OleDbDestination> </ OleDbDestination> |
变量 | <Variable> </ Variable> |
参数 | <参数> </参数> |
You can refer to the script above to check the node hierarchy and how tasks and components are linked together. 您可以参考上面的脚本来检查节点层次结构以及如何将任务和组件链接在一起。
In this article, we illustrated the BimlExpress Import packages tool and how we can use it to convert existing SSIS projects or standalone packages into Biml scripts. In my opinion, using this tool can be the best way to learn this markup language since it lets you convert SSIS packages and analyze the generated script to see how each object was serialized.
在本文中,我们说明了BimlExpress导入软件包工具,以及如何使用它将现有的SSIS项目或独立软件包转换为Biml脚本。 我认为,使用此工具可能是学习此标记语言的最佳方法,因为它使您可以转换SSIS包并分析生成的脚本以查看每个对象的序列化方式。
Converting SSIS packages to Biml scripts |
将SSIS包转换为Biml脚本 |
翻译自:
ssis 列转换
转载地址:http://hpnwd.baihongyu.com/