Tech tip: making SSIS Project Connections generate correctly using BIML Express

A bit more of a technical view on things today. In order to stay up to date with the latest when it comes to generating ETL for the Microsoft stack (SSIS), I recently upgraded from Visual Studio 2013 with BIDS Helper 1.6.6. to Visual Studio 2015 with BIML Express. And this means a lot of regression testing for years and years of increasingly complex BIML and C# scripts. As it turns out it wasn’t too bad and the differences between Visual Studio 2013 and Visual Studio 2015 aren’t massive (I could even open the solution using both versions, that’s rare with Visual Studio).

Moving from BIDS Helper to BIML Express is a slightly bigger changes. BIML Express has been positioned by Varigence as the new development plug-in to use BIML for ETL generation. It basically supersedes BIDS Helper in this respect, although the BIDS Helper plug-in can still be used (even in conjunction with BIML Express) and has a host of useful features supporting broader BI development within Visual Studio (some of them are addressed in VS2015 though). If the only thing you’re interested in is BIML, then BIML Express will suffice. The biggest selling point is the IntelliSense option (and code formatting) which makes development a lot easier. I am happy to have moved to this new solution.

However, there were two bugs that kept everything from working properly – both occurring when generating Project Connections.

• When generating a Project Connection with a Username / Password connection string, the password isn’t stored. This may not even be a bug, but it used to work like this (and I want it to).
• When generating a Package using a Package Connection, the connection isn’t recognised in the package. The Project Connections are created, but the sources and destination objects are not mapped. This is a far more serious issues, which has been acknowledged as a bug and has been reported to be fixed in a next release of BIML Express.

So, in the meantime here’s the workaround to still use this functionality and generate Project Connections that are correctly linked to Packages when you generate them. The (temporary) solution here is to:

• Make the BIML store the password as an expression of the Project Connection. This can either be hard-coded, or by using a Project Parameter. Either way, the ‘password’ expression is added to the (central) Project Connection so it works for all packages that use that connection.
• Reference the Project Connection by its GUID when generating Packages.

To implement this define connections as usual (top-level, within the <BIML> tags, before the <Packages> tag). I mapped these connections to variables provided by C#.

<Connections>
  <Connection Name="METADATA" CreateInProject="true" ConnectionString="<#=connectionStringMETADATA#>"/>
  <Connection Name="STG" CreateInProject="true" ConnectionString="<#=connectionStringSTG#>"/>
  <Connection Name="INT" CreateInProject="true" ConnectionString="<#=connectionStringINT#>"/>
</Connections>

This is all as it used to be, but to address the above two issues I added a reference within the <Package> tag (again) to identify the connection and set the connection password property. You can add the GUID of the connection as an ID here to make the SSIS package reference to the correct Project Connection. When BIML Express is updated you can take this out again. The GUID of the connection can be viewed in the properties of the Project Connection in the Connection Managers, and copied here.

<Connections>
 <Connection ConnectionName="METADATA" Id="{6A486C3B-A2C0-4338-8929-A966D2C201EB}">
 <Expressions>
   <Expression ExternalProperty="Password">"password"</Expression>
 </Expressions>
 </Connection>

 <Connection ConnectionName="STG" Id="{5CBCE390-8497-48D6-B9FB-6209F7041896}" >
 <Expressions>
 <Expression ExternalProperty="Password">"password"</Expression>
 </Expressions>

 <Connection ConnectionName="INT" Id="{19DC3EE4-9EA3-43FC-BDDD-B3EEEF187958}">
 <Expressions>
 <Expression ExternalProperty="Password">"password"</Expression>
 </Expressions>
 </Connection>
</Connections> 

This will generate SSIS packages and their Project Connections in a way that the source and destinations are mapped correctly. The underlying issue is that at present BIML seems to lookup an incorrect GUID which doesn’t match the one for the designated connection. This is a way to get around that.