ssis - 自定义SSIS工作流程任务

Question

我有大量的容器都遵循相同的基本前提:



当我从远程数据库中提取数据时，我首先清空了收集器表，将数据从远程数据库复制到收集器，对收集器中的行进行计数，如果有足够的行，那么我将合并到真实表中。如果没有，我会发送一封包含错误消息的电子邮件。

我不想做一遍又一遍的重复，我想做一个自定义组件。我认为这只是我要制作的过滤器组件，但是我不确定的是如何复制Data Flow Task片段。有人可以指出我什么好例子，或者只是让我知道我想做的事是不可能的吗？

Answer 1

当我看到这样的问题时，Biml倾向于为创建简单，可重复的解决方案提供最低的障碍。 Biml是免费的，您只需花费注册电子邮件即可将BimlExpress安装到您正在使用的任何Visual Studio / SSDT版本中。

我假设我将从AdventureWorks2014 Sales.Currency表中收集数据，并将其传输到tempdb中名为dbo.SalesCurrency的表中。

我定义为

CREATE TABLE dbo.SalesCurrency
(
    CurrencyCode nchar(3) NOT NULL
,   Name nvarchar(50) NOT NULL
,   ModifiedDate datetime NOT NULL
);

鉴于此，让我们看一些Biml概念。 Biml是一种基于XML的方言，描述了商务智能工件(然后再介绍一些)。如果您曾经通过脚本和标签的组合进行过经典的ASP开发，则它是一个类似的概念，但是由于.NET集成而变得更好。

<# #>这是一个多行块

<#= #>是单行表达式

太好了，该如何使用？假设您已经安装了BimlExpress，请打开一个SSIS项目，然后右键单击“项目”部分，然后选择 Add New Biml File。重复两次，我们将重命名第二次。第一个是司机，第二个是工人。

脑筋

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Data Source=localhost\dev2017;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />        
        <OleDbConnection Name="Target" ConnectionString="Data Source=localhost\dev2017;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />        
    </Connections>
<#
string sourceQuery = "SELECT * FROM Sales.Currency;";
string targetSchemaTable = "[dbo].[SalesCurrency]";
string templateName = "so_56050574_include.biml";
dynamic customOutput;
#>
    <Packages>
        <#= CallBimlScriptWithOutput(templateName, out customOutput, sourceQuery, targetSchemaTable) #>
    </Packages>
</Biml>

第一行只是xml namespace 。

下一个块，即“连接”集合，我定义了“源”和“目标”连接。我很有创造力，将其命名为 Source和 Target
接下来的几行看起来很像C#，因为它们是。我定义了源查询，完全限定的目标表名称，包括的方括号和模板文件的名称。最后的变量customOutput没有在这里使用，但是它是一个袋子，它使我能够从模板文件传回信息，即它所构建的SSIS包的名称。

然后，我定义一个Packages集合并制作一个包。我制作的程序包由发送到 CallBimlScriptWithOutput的任何内容定义，然后使用我刚定义的变量。

它看起来很复杂，但事实并非如此。我之所以喜欢这种方法，是因为它不是将这些值硬编码到驱动程序中，而是允许我采用元数据驱动的方法进行开发。我可以从电子表格，Sharepoint列表，Web服务中查找这些值，无论我感觉如何(或我的客户提供的存储库)。

工人比姆

我将此文件命名为so_56050574_include.biml，尽管其中有很多文本，但它相当简单。

第一行在Biml设计过程中帮助Intellisense。
接下来的两行指定这些变量将被传入-就像函数调用一样。在此文件范围内，我将可以像.NET变量一样使用它们。

接下来的几行有点时髦，但是SSIS不喜欢重复的名称，也不喜欢名称中的“坏”字符。我将包名称指定为Populate Collector，然后使目标表对于SSIS安全。在文件的底部，您将看到我做了一个名为 MakeSsisSafeName的小方法，该方法用于清理程序包名称。

我创建了一个Package，并给它起了好名字。该包裹有一个容器。在容器中，我创建了一些SSIS变量，这些都是我要做的工作。该容器具有执行SQL任务->数据流任务->执行SQL任务->执行SQL任务->发送邮件任务的任务

<#@ template designerbimlpath="/Biml/Packages" #>
<#@ property name="SourceQuery" type="string" #>
<#@ property name="TargetSchemaTable" type="string" #>

<#
string packageName = string.Format("Populate Collector {0}", MakeSsisSafeName(TargetSchemaTable));
CustomOutput.PackageName = packageName;
#>
<Package Name="<#= packageName #>" ConstraintMode="Linear">
    <Tasks>
        <Container Name="SEQC Collector" ConstraintMode="Parallel">
            <Variables>
                <Variable Name="RowCount" DataType="Int64">0</Variable>
                <Variable Name="QueryEmpty" DataType="String">TRUNCATE TABLE <#=TargetSchemaTable#></Variable>
                <Variable Name="QueryCount" DataType="String">SET NOCOUNT ON; SELECT COUNT_BIG(1) AS rc FROM <#=TargetSchemaTable#></Variable>
                <Variable Name="QuerySource" DataType="String"><#=SourceQuery#></Variable>
                <Variable Name="TargetSchemaTable" DataType="String"><#=TargetSchemaTable #></Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL Name="SQL Empty Collector Table" ConnectionName="Target">
                    <VariableInput VariableName="User.QueryEmpty" />
                </ExecuteSQL>
                <Dataflow Name="DFT Populate Collector Table">
                    <Transformations>
                        <OleDbSource Name="OLESRC Query" ConnectionName="Source">
                            <VariableInput VariableName="User.QuerySource" />
                        </OleDbSource>
                        <OleDbDestination Name="OLEDST Target" ConnectionName="Target">
                            <TableFromVariableOutput VariableName="User.TargetSchemaTable" />
                        </OleDbDestination>
                    </Transformations>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Empty Collector Table.Output" EvaluationValue="Success" />
                        </Inputs>
                    </PrecedenceConstraints>
                </Dataflow>
                <ExecuteSQL Name="SQL Count Collector Table Rows" ConnectionName="Target" ResultSet="SingleRow">
                    <VariableInput VariableName="User.QueryCount" />
                    <Results>
                        <Result Name="0" VariableName="User.RowCount" />
                    </Results>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="DFT Populate Collector Table.Output" EvaluationValue="Success" />
                        </Inputs>
                    </PrecedenceConstraints>
                </ExecuteSQL>
                <ExecuteSQL Name="SQL Merge Collector Data" ConnectionName="Target">
                    <DirectInput>SELECT 1; -- simulate merge</DirectInput>
                        <PrecedenceConstraints>
                            <Inputs>
                                <Input OutputPathName="SQL Count Collector Table Rows.Output" EvaluationOperation="ExpressionAndConstraint" EvaluationValue="Success" Expression="@[User::RowCount] &gt; 0" />
                            </Inputs>
                        </PrecedenceConstraints>
                </ExecuteSQL>
                <!--
                <SendMail Name="Send Mail" ToLine="Foo@bar.com" ConnectionName="Target" Subject="Subject line">
                    <DirectInput>Body here, I think</DirectInput>
                        <PrecedenceConstraints>
                            <Inputs>
                                <Input OutputPathName="SQL Count Collector Table Rows.Output" EvaluationOperation="ExpressionOrConstraint" EvaluationValue="Success" Expression="@[User::RowCount] == 0" />
                            </Inputs>
                        </PrecedenceConstraints>
                </SendMail>
                -->
                <ExecuteSQL Name="SQL Pretend I send mail" ConnectionName="Target">
                    <DirectInput>SELECT 2; -- simulate merge</DirectInput>
                        <PrecedenceConstraints>
                            <Inputs>
                                <Input OutputPathName="SQL Count Collector Table Rows.Output" EvaluationOperation="ExpressionAndConstraint" EvaluationValue="Success" Expression="@[User::RowCount] ==0" />
                            </Inputs>
                        </PrecedenceConstraints>
                </ExecuteSQL>
            </Tasks>
        </Container>
    </Tasks>

</Package>

<#+
private static string MakeSsisSafeName(string name)
{
    return name.Replace("/", "_").Replace("\\", "_").Replace(":", "_").Replace("[", "_").Replace("]", "_").Replace(".", "_").Replace("=", "_").Trim();
}
#>

右键单击BimlScript大脑文件，然后选择生成SSIS包



那应该建立一个这样的程序包，嘿，它起作用了!



未涵盖的内容

我不知道您实际如何使用它。也许您有一个带有许多容器的大包装，而您的愿景是仅按下按钮并添加另一个模板容器。 Biml不会那样做。它不会合并两个SSIS程序包-它会覆盖当前定义的程序包。但是，按照我定义所有这些的方式，您应该能够复制生成的Container并将其粘贴到现有的SSIS包中-假设它具有两个名为Source和Target的连接。

连接也可能很棘手。如果要从N个源服务器收集数据，则可能需要一种循环机制来更改Source值。那不难。但是，如果要为每个收集器拉回的源数据具有不同的签名，则您需要每个定制的数据流任务。

正在发送电子邮件。我没有SMTP连接，所以我最好猜测一下Send Mail的外观，然后将其注释掉 <!-- ... -->您需要在Brains软件包中为SMTP服务器添加一个Connection，然后配置SendMail使用它的任务。然后删除我的“SQL假装我发送邮件”任务。

最后，您会注意到在工人Biml中重复了这些名称。这告诉引擎应该如何接线。如果您不喜欢我所说的话，则需要在两个地方进行更改。搜索和替换将很方便;)

问有关自定义工作流程任务的问题-回答它

精细。糟透了DataFlow东西进入了COM对象，并且使用它们并不愉快。提供查询或源表时，您需要检查元数据，添加/删除列以及许多文档记录较差且繁琐的工作。这只是通过接口(interface)构建“常规”包。一旦解决了该问题，您便正在考虑将该逻辑封装到一个自定义组件中，该组件以前在Codeplex上有足够的示例记录在案，但现在已经死了，我不知道它是否已迁移到github。哦，自定义任务和组件尤其取决于版本，因此您可以针对各种二进制文件构建文件，以获取每个文件的dll。然后，您可能需要构建UI组件，以帮助人们配置SSIS任务/组件。然后，您需要担心在每个开发人员的计算机上交付和安装它。和服务器安装。

或者，我可以通过Biml一次定义它并完成。