本文介绍了在“连接管理器”中使用已经创建的连接。进入Biml脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在SSIS连接管理器中已有源连接。这是一个自定义连接（不是oledb或任何其他标准连接类型）。

我在Internet上看到了两种方法，但都无法使用：我做了一个示例，以连接到Advanturwork数据库，下面是项目的屏幕截图，错误消息以及两种方法的代码。

这里是项目文件。

这是连接管理器XML文件（MyConn.conmgr）。

第二个Biml代码（方式2）：

 <＃@ import namespace = System＃> 
<＃@ import namespace = System.Data＃> 
 
< Biml xmlns = http://schemas.varigence.com/biml.xsd> 
<＃var Source =（AstDbConnectionNode）RootNode.Connections [ MyConn]; ＃> 
< Packages> 
< Package Name = Package1 ConstraintMode = Linear> 
< Tasks> 
< Dataflow Name = DF1> 
< Transformations> 
< OleDbSource Name =获取数据 ConnectionName = Source> 
< ExternalTableInput Table = Person.Person /> 
< / OleDbSource> 
< / Transformations> 
< / Dataflow> 
< /任务> 
< / Package> 
< / Packages> 
< / Biml>

错误消息是相同的：

PS示例中的MyConn是一个oledb，但在实际项目中，它是一个自定义连接，该连接已在连接管理器中定义，我只需要在biml脚本中使用（称为它）即可。

谢谢，
Ziad

解决方案

好，现在我已经足够理解这个问题。

在SSIS项目中，您有一个项目级别的连接管理器 MyConn。这是在Biml之外生成的。

在Biml文件中，您想引用现有的物理连接管理器。不幸的是，这不可能直接实现。 Biml定义了项目中的所有工件以及其中的依赖项。 Biml引擎使用所有这些来创建输出-在这种情况下，将是

包装

[项目级别的连接管理器]

[SSIS项目文件]

[可选]

您将需要获取输出（连接管理器）并将其反向工程为Biml。有两种简单的方法可以做到这一点：BimlOnline.com或BimlStudio。 BimlOnline仍处于测试阶段，如果它对您有用，那就太好了。否则，您
需要下载/注册

里夫斯对，但简短的是所有静态文件都将首先编译并可供参考。然后，其中包含代码的任何Biml文件（例如Packages.biml）都有。

您可以根据需要进行多次选择-我的典型项目有一个Connections.biml文件，通常是Packages.biml（用于构建工作包）和Master.biml（用于处理程序包的编排）。

导入文件

有多种将文件导入Biml的机制，但最常见的两种是 include 指令和 CallBimlScript 函数。可以将Include视为宏扩展-<＃@ include file = Connection.biml＃> 将被该文件的内容替换。

<＃= CallBimlScript（ Connection.biml）＃> 可以认为是一个函数打电话-是的。我可以在传递给我的引用文件中定义属性（参数），以控制生成的biml。

在任何一种情况下，实体都不存在/在biml项目 eg RootNode.Connections [ MyConnection] 中是可引用的，因为它们是在此层中编译的。

BimlStudio

如果您决定购买BimlStudio，第三种方法是将Connections.biml文件设置为实时biml脚本（或执行Connections.biml文件）以具有持久的内存中对象供参考。

I have an already source connection in SSIS "Connection Managers". It is a custom connection (not a oledb or any other standard connection types). Is it possible to reuse it (i.e call it) in the Biml script without writing the connetion string again?

I saw two ways on the internet but neither worked with me: I did an example to connect to Advanturwork database, bellow are the screenshots of the project and the error message as well as the code of the two ways.

Here is the porject files.

This is the connection Managers XML file (MyConn.conmgr).

Now comes the Biml code (way 1):

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Connections>
                <Connection ConnectionName="MyConn" Id="FB58654F-7992-4DB2-9057-38595A89B6BF"></Connection>
            </Connections>
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="MyConn">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error I get is here:

The second Biml code (way 2):

<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var Source = (AstDbConnectionNode)RootNode.Connections["MyConn"]; #>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="Source">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error message is the same:

P.S. MyConn in the example is an oledb but in the real project it is a custom connection which is already defind in the "Connection Managers" and I need just to use (call it) in the biml script.

Thanks,Ziad

解决方案

Ok, now I see enough to understand the question.

Within your SSIS project, you have a project level connection manager "MyConn". This was generated outside of Biml.

Within your Biml files, you would like to reference the existing physical connection manager. That unfortunately, isn't directly possible. Biml defines all the artifacts within a project and dependencies therein. The Biml engine uses all of that to create output - which in this case would be

Packages
[Project level Connection Managers]
[SSIS Project file]

[optional]

You would need to take the output (connection manager) and reverse engineer that into Biml. There are two easy approaches to this: BimlOnline.com or BimlStudio. BimlOnline is still in beta and if it works for you, fantastic. Otherwise, you need to download/register for BimlStudio and use the 15 day trial and reverse engineer the existing SSIS project (Integration Services Project 1.dtproj). That should get the Biml extracted for the custom connection manager.

If it doesn't, then it gets ugly as you have to guess/mangle your way through the syntax for your connection manager or try emailing [email protected] and see if they can't help you.

Now, once you have a working connection string, then you'll need to fix the above Biml where you use it.

First biml

In your first example, you have <OleDbSource Name="Get Data" ConnectionName="MyConn"> That will only work if you have also defined your Connection within a Connections collection. In the following Biml, I define MyConn within my Connections collection. Then, under the Packages collection, in package Package1, I provide an explicit reference to that connection manager and specify the GUID it should be assigned. This may or may not be needed in your package but my general experience is that it will not be needed.

<Biml>
    <Connections>
        <Connection Name="MyConn" ConnectionString="ABC" />
    </Connections>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
        <Connections>
            <Connection ConnectionName="MyConn" Id="FB58654F-7992-4DB2-9057-38595A89B6BF"></Connection>
        </Connections>
        </Package>
    </Packages>
</Biml>

As called out way below under ramblings, you can physically separate the Connections collection into a separate file and include/multi-select it.

Second biml

Here, you're creating a C# variable, Source that is an the object version of our connection. This assumes that in an earlier tier, we have explicitly created a Connection called MyConn. Otherwise, you'll get a null reference exception because you are trying to access a member of a list (Connections) that doesn't exist.

The second issue will be when you go use it <OleDbSource Name="Get Data" ConnectionName="Source"> The Biml compiler will say that it cannot find a reference to the connection "Source". Instead, what you're looking for here is ConnectionName="<#= Source.Name #>" That directive uses the Name property of our variable Source and calls the Write method <#= #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var Source = (AstDbConnectionNode)RootNode.Connections["MyConn"]; #>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="Source">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Earlier but possibly helpful Biml ramblings

I think what you're asking about is code re-use.

Within your SSIS project, you have two Biml files: Connections.biml and Packages.biml

Connections.biml looks something like (approximate)

<Biml>
    <Connections>
        <Connection Name="MyConn" ConnectionString="ABC" />
    </Connections>
</Biml>

and we have the actual Packages biml defined within the question above. The Packages.biml file can either have a Connections collection defined within it or we can reference and existing file. There are multiple ways to do this with BimlExpress.

Multi-click option

In Visual Studio, multi-select (Control, Left-Click) the Connections.biml and Package.biml files and choose Generate SSIS Packages.

Reeves has a great explanation of tiering and how all that works but the short is that all the static files will compile first and be available for reference. Then any Biml files with code in them like Packages.biml has.

You can multi-select as many as you want - my typical project has a Connections.biml file and usually a Packages.biml (which builds out worker packages) and a Master.biml (which handles the orchestration of package execution).

Importing files

There are different mechanisms for importing files into Biml but the two most common will be the include directive and the CallBimlScript function. Include can be thought of as a macro expansion - the <#@ include file="Connection.biml"#> is going to be replaced with the contents of that file.

<#=CallBimlScript("Connection.biml")#> can be thought of as a function call - because it is. I could have properties (arguments) defined in the referenced file that I pass in to control the biml that is generated.

In either case, the entities won't yet exist/be reference-able within the biml project e.g. RootNode.Connections["MyConnection"] because they are being compiled in this tier.

BimlStudio

And if you decide to purchase BimlStudio, a third approach is to set your Connections.biml file as a live biml script (or execute the Connections.biml file) to have persistent, in-memory objects for reference.

这篇关于在“连接管理器”中使用已经创建的连接。进入Biml脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！