In SQLServer Integration Services, the use of Expressions to replace properties at runtime is very powerful. You can use it in combination with variables to pass parameters to queries, determine runtime specific dates, and much more.

However, while testing SQLServer 2019, I came across the following issue when replacing the ConnectionString property of a Connection Manager using Expressions. This was addressed working with Microsoft and a fix will be released in a future SQLServer 2019 CU for this specific problem.

Scenario

Imagine that you have multiple databases that are identical in schema, but have different content. This could occur when e.g. an application’s back-end database is designed by company. For global reporting and analysis, you want to export all these different databases to a BI system. An advantage could be that it keeps the database geographically close to the user’s location.

Implementation

When you would create a specific extract package for each database, there quite some disadvantages:

  • The creation of the package is a lot of work
  • When a change is made to all database, you’d have to change all packages
  • You also need to be sure that all packages contain the same extract logic

The only difference between all the package should be the connection string to the source database. To change this dynamically, you can take advantage of an SSIS Expressions on the OLEDB Connection Manager.

In practice, you would store those connection strings in a configuration table, which is read in step “Get All Source Systems” and loaded into a Recordset Object variable.

In the Foreach Loop Container, loop through each record in this recordset and load the Connection String into the variable User::SourceSystemConnectionString.

You can replace the Connection Manager’s ConnectionString property using the Expression for the ConnectionString property : @[User::SourceSystemConnectionString]

Issue

So far, so straightforward. This technique used to work in SSIS 2017, but when executed in SSIS 2019, the package failed with following error message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E73.
An OLE DB record is available. 
Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E73 
Description: "Format of the initialization string does not conform to the OLE DB specification.".

At first, I thought it was a change in the OLEDB specification, as stated by the error message, but having tried all possible options, I had to ask Microsoft to investigate. For reference, the target databases were Azure SQL DB and the connection string was in this format:

Data Source=tcp:axis0.database.windows.net,1433;Initial Catalog=ORDERS;User ID=axis0_reader;Password=xxxxxxx;Provider=SQLNCLI11.1;Auto Translate=False;

The error is confirmed to be a bug and will be included in one of the next CU for SQLServer 2019. I’ll update this article with the bug number and KB number for reference when available. Case closed!

Read More

If you’re interested in the SQLServer platform and its different features, keep an eye on this blog.
A previous article on Master Data Services in SQLServer 2019 describes how to integrate MDS in your BI ETL flow : Mastering New Data