CODEWITHSUNDEEP
ssisado.netbiml
drb
drb

Reputation: 15

BIML reuse a connection already existing in project

I have created a biml script, and I want to see if a reuse of an existing connection in SSIS project is possible ?

Here is a simple script (by the way which doesn't work) I just created : 

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <CustomSsisConnection CreationName="SrcConn.conmgr" Name="SourceConnection"/>
</Connections>
<Packages>
    <Package Name="SRC_package" >
        <Tasks>
            <Dataflow Name="Data Flow Task">                    
                <Transformations>
                    <AdoNetSource ConnectionName="SourceConnection" Name="ADO_SRC" >
                        <DirectInput>

                        </DirectInput>
                    </AdoNetSource>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>

Upvotes: 0

Views: 1392

Answers (2)

Matthias
Matthias

Reputation: 373

Yes, you can reuse an existing SSIS project connection manager in your BIML Script, if you are careful:

  1. Find the GUID of the existing project connection manager by viewing its code (Do not look in the property pages, the GUID is not displayed there).

  2. In the BIML Script Re-Create the same project connection manager under the main BIML node. I will call this connection "fake project connection manager". Use the same connection string as the existing one.

    <Biml>
<Connections>
      <Connection Name="EXISTING_CONMANAGER" CreateInProject="true" ConnectionString="Data Source=....;" /> 
</Connections>

  3. Under the Package Node, recreate again the same Project Connection Manager, this time by using the GUID of the real existing one.

          <Package Name= "myPackage" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">

      <Connections>
        <Connection ConnectionName="EXISTING_CONMANAGER" Id="{GUID_READ_FROM_EXISTING_CONMGR_FILE}"></Connection>
      </Connections>

  4. Now, if you biml this script, a new project connection manager is created, but it uses still the same GUID as the one already existed. Furthermore, after running the script, you are asked if the existing manager should be reloaded or not. You should select (NO), but both possible answers are fine - the packages now use the one that already existed in the solution.

I know, it's a bit crazy, but it works for me. By specifiying the GUID in the Id Node, the GUIDS of the recreated project connection managers are set to the values already set in the existing connection manager files.

Upvotes: 1

billinkc
billinkc

Reputation: 61221

No, you cannot have have Biml use a connection that is defined outside the artifacts the Biml compiler will know about.

It sounds like the root issue is that you would like to have the Biml that create a ado.net connection pointing to a postgresql database I don't have one of those handy but I'd take a stab at the syntax based on the connection strings

<Connections>
    <AdoNetConnection 
         Name="MyConnection"
         Provider="Devart.Data.PostgreSql.PgSqlConnection, Devart.Data.PostgreSql, Version=7.4.521.0, Culture=neutral, PublicKeyToken=00MyKey00" 
         ConnectionString="User Id=MyUserName;Password=MyPassword;Host=MyServerName;Database=MyDatabaseName;Persist Security Info=False;Unicode=True;Default FetchAll=true;" 
         DelayValidation="true" />
</Connections>

The best way to get the proper Provider details is to download BimlStudio or create a BimlOnline and use the reverse engineer/import package functionality of the paid for tool. BimlStudio/Mist has a limit of 5 free reverse engineerings and BimlOnline is still in Beta so my experience there has been hit or miss. Personally, I would minimize the package to just a connection manager (select all, delete, save as ImportMe.dtsx and then import that). The idea being the less that needs to be reverse engineered, the better odds of success.

Upvotes: 1

Related Questions