使用C＃脚本扩展Biml-白红宇

使用C＃脚本扩展Biml

阅读量：2518 次

发布时间：2019-05-11

本文共 10237 字，大约阅读时间需要 34 分钟。

In our in the Biml series, we have explained what Biml is, and how to use this language to generate SQL Server Integration Services (SSIS) packages. In this article, we will explain BimlScript, which is an extension of this markup language with VB or C# scripts.

在Biml系列中，我们解释了Biml是什么，以及如何使用该语言生成SQL Server Integration Services（SSIS）程序包。在本文中，我们将解释BimlScript，它是使用VB或C＃脚本对此标记语言进行的扩展。

When working as a data engineer or business intelligence developer, sometimes you may need to build a number of packages which require a lot of repetitive work (i.e., if we need to transfer multiple tables with differents schema to another database by applying the same transformations over each table), this task is time-consuming using SSIS or even using static Biml code. For this reason, BimlScript was developed.

在以数据工程师或商业智能开发人员的身份工作时，有时您可能需要构建许多需要大量重复工作的程序包（即，如果我们需要通过在数据库上应用相同的转换将具有不同模式的多个表转移到另一个数据库，每个表格），使用SSIS甚至使用静态Biml代码都需要花费大量时间。因此，开发了BimlScript。

什么是BimlScript？ (What is BimlScript?)

BimScript is an automation tool to generate, control, and manipulate Biml scripts. It can read database metadata, loop over database objects, and replace static values with expressions.

BimScript是用于生成，控制和操纵Biml脚本的自动化工具。它可以读取数据库元数据，遍历数据库对象，并用表达式替换静态值。

In BimlScript, you have to use one of the following code blocks to write a script or comment:

在BimlScript中，您必须使用以下代码块之一来编写脚本或注释：

Code block	Type	Description
<# #>	Control	Used to implement a control logic such as reading database metadata
<#= #>	Text	Used to return a string value
<#@ #>	Directive	Used to add compiler instructions
<#+ #>	Class	Used to create a C#/VB class
<#* *#>	Comment	Used to add a comment

代码块	类型	描述
<＃＃>	控制	用于实现控制逻辑，例如读取数据库元数据
<＃=＃>	文本	用于返回字符串值
<＃@＃>	指示	用于添加编译器指令
<＃+＃>	类	用于创建C＃/ VB类
<＃* *＃>	评论	用于添加评论

( )

使用BimlScript自动化构建软件包 (Automating building packages using BimlScript)

Assuming we need to migrate all tables that belong to the Person schema within the AdventureWorks database. For each table, we need to add a derived column that contains the current date and time (using SSIS GETDATE() function).

假设我们需要迁移AdventureWorks数据库中属于Person模式的所有表。对于每个表，我们需要添加一个包含当前日期和时间的派生列（使用SSIS GETDATE（）函数）。

To do that, using BimlScript, first add a new Biml Script file to your solution. Next, you should do the following steps:

为此，请使用BimlScript，首先将新的Biml脚本文件添加到解决方案中。接下来，您应该执行以下步骤：

Specifying the script language (C# or VB) and import needed assemblies
指定脚本语言（C＃或VB）并导入所需的程序集

Retrieve tables metadata from AdventureWorks database
从AdventureWorks数据库中检索表元数据

Loop over tables and generate a package for each table
遍历表并为每个表生成一个包

Define connection managers within the script
在脚本中定义连接管理器

Add and configure a data flow task within each package
在每个包中添加和配置数据流任务

In this section, we will explain how to do each step. Then we will generate the packages after combining all parts.

在本节中，我们将说明如何执行每个步骤。然后，我们将所有部分合并后生成软件包。

配置脚本 (Configuring script)

To extend Biml with C# or VB script, you should first add a directive block to specify the language you want to use. Then you have to import all the assemblies you need to use within the script. Note that this part of code must be added outside the main element <Biml> as following:

要使用C＃或VB脚本扩展Biml，首先应添加一个指令块以指定要使用的语言。然后，您必须导入脚本中需要使用的所有程序集。请注意，这部分代码必须添加到主元素<Biml>之外，如下所示：

<#@ template language="C#" hostspecific="true"#><#@ import namespace="System.Data" #>

从AdventureWorks数据库中检索表元数据 (Retrieve tables metadata from AdventureWorks database)

To retrieve table metadata from a database, you should add two control blocks before the main <biml> element in the script; the first block is to establish a connection and the other is to retrieve the table’s metadata from it. You should use a similar code:

要从数据库中检索表元数据，您应该在脚本中主要的<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
    
     {"Person"},null,ImportOptions.ExcludeViews); #>

You can read more about the Import Metadata in using GetDatabaseSchema .

您可以使用GetDatabaseSchema阅读有关导入元数据的更多信息。

定义连接管理器 (Define connection managers)

Since all packages will use the same connection managers, we will define them at the project level. To do that, we will define them outside of <packages> element as mentioned below:

由于所有软件包都将使用相同的连接管理器，因此我们将在项目级别对其进行定义。为此，我们将在<packages>元素之外定义它们，如下所述：

遍历表格
(Loop over tables
)

As described in the previous articles in this series, all packages should be defined within <packages> element, and since we are looking to generate multiple packages, we should implement the loop logic within this element. We need a loop over tables retrieved, add a package for each table where the table name is “Extract_” + table name. We should use the following code to achieve that:

如本系列前几篇文章所述，所有包都应在<packages>元素内定义，并且由于我们希望生成多个包，因此应在此元素内实现循环逻辑。我们需要对检索到的表进行循环，为表名为“ Extract _” +表名的每个表添加一个包。我们应该使用以下代码来实现：


    
         
     
              
              
          
         
     
              <# foreach (var table in metadata.TableNodes.Where(x => x.Schema.Name == "Person")) { #>        
      
                
              <# } #>

Note that we have used a Text block to evaluate the package name as expression:

请注意，我们已使用Text块将包名称评估为表达式：

添加和配置数据流任务
(Add and configure Data Flow Task
)

For each package, we should add a Data Flow task that contains an OLE DB Source, one Derived Column transformation, and an OLE DB Destination. In this example, we will extract data from the AdventureWorks2017 database and load it into tempdb. We need to add the following biml code within <package> element:

对于每个包，我们应该添加一个数据流任务，其中包含一个OLE DB源，一个派生列转换和一个OLE DB目标。在此示例中，我们将从AdventureWorks2017数据库中提取数据并将其加载到tempdb中。我们需要在<package>元素中添加以下biml代码：

生成包
(Generating package
)

The whole BimlScript code should look like:

整个BimlScript代码应如下所示：

<#@ template language="C#" hostspecific="true"#><#@ import namespace="System.Data" #><# 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
    
     {"Person"},null,ImportOptions.ExcludeViews); #>
     
          
      
               
               
           
          
      
               <# foreach (var table in metadata.TableNodes) { #>        
       
                    
        
                         
         
                              
          
                                   
           
                                        
                                    
                                   
           
                                        
            
                                             
             
              GETDATE()
                                         
                                    
                                   
           
                                        
                                    
                               
                          
                     
                
               <# } #>

You can check the Biml generated from this script in the preview window available in the Visual studio editor:

您可以在Visual Studio编辑器中的预览窗口中检查从此脚本生成的Biml：

The following code is generated from the script above:

从上面的脚本生成以下代码：

To generate packages, right-click on the Biml script file in the solution explorer, and click on “Generate SSIS packages”:

要生成软件包，请在解决方案资源管理器中右键单击Biml脚本文件，然后单击“生成SSIS软件包”：

Generating SSIS packages from Biml script

After Biml expansion is completed, you can see that multiple packages are added to the solution explorer:

Biml扩展完成后，您可以看到多个软件包已添加到解决方案资源管理器：

If we open one of these packages, we can see that it contains a data flow task and two connection managers:

如果打开其中一个程序包，则可以看到它包含一个数据流任务和两个连接管理器：

Also, we can see that all components are created successfully within the data flow task:

此外，我们可以看到在数据流任务中成功创建了所有组件：

结论 (Conclusion)

BimlScript is a very powerful extension for Biml. It prevents doing a lot of repetitive work and decreases development time. In this article, we have explained what BimlScript is and why it should be used. Also, we showed how to generate multiple packages from a simple BimlScript code.

BimlScript是Biml的非常强大的扩展。这样可以避免进行很多重复的工作并减少开发时间。在本文中，我们已经解释了BimlScript是什么以及为什么要使用它。另外，我们展示了如何从一个简单的BimlScript代码生成多个包。