BimlScript C# code nuggets to read SSIS project parameters

Question

There's plenty of online documentation showing how to read SSIS project parameters via one of:

• Biml tags
• SSIS C# script task

but I've spent over six hours trying to find a way to do it from C# code within BimlScript. Granted, I'm a Biml n00b, so maybe I haven't found a solution because it's so easy nobody's talking about it.

My Biml (sensitive data replaced with '***'):

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

<#
  string connString = "Data Source=***;Provider=***;Persist Security Info=True;Location=***;uid=***;pwd=" + #>@[$Project::PW]<#;
  OleDbConnection db2Conn = new OleDbConnection(connString);
  string queryString = "SELECT * FROM SYSIBM.SYSTABLES WHERE DBNAME = '***' WITH UR";

  OleDbCommand myCommand = new OleDbCommand(queryString);
  myCommand.Connection = db2Conn;
  db2Conn.Open();
  myCommand.ExecuteReader();
  db2Conn.Close();
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="DB2Test" />
  </Packages>
</Biml>

This gives the error:

Operator '+' cannot be applied to operands of type 'string' and 'void'

which makes me think that string concatenation resolves before it can get the project parameter.

Note that if I hard-code the password in the connection string, it works fine.

Answer 1

I'm not entirely sure this is possible, as to my knowledge the C# is converted into Biml and then that Biml is used to create the SSIS package.

In my projects, I create the whole SSIS project in Biml and recreate the entire solution each time I need to make changes. To facilitate this, I created a C# file called Variables.cs - that I reference in each Biml file using <#@ code file="Variables.cs" #> that contains all the values and functions to return DataTables that I need to reference in the Biml.

For Example:

<# foreach (DataRow row in ConnectionVariables.GetTranConnConfig("Target","").Rows) { #>
    <OleDbConnection Name="TranTarget_<#=row["TargetDatabaseName"]#>"
                     ConnectionString="Data Source=<#=row["TargetServerName"]#>;Initial Catalog=<#=row["TargetDatabaseName"]#>;Integrated Security=SSPI;Provider=<#=ConnectionVariables.GetConfigSQLProvider()#>;"
                     CreateInProject="true"
                     >
        <Expressions>
            <Expression ExternalProperty="ConnectionString"
                    >@[$Project::TranTarget_<#=row["TargetDatabaseName"]#>_Conn]</Expression>
        </Expressions>
    </OleDbConnection>
<# } #>

Which uses both the results of queries that use metadata in a config database and project specific values hardcoded in the C#, such as the server and database that holds the metadata, the name of the project to be created etc. By doing this, I can change one or two things in Variables.cs and use the generic Biml solution across different environments.