Sometimes, the things we do can seem a bit convoluted. This is one of those cases. Without bothering to explain how we got to this point (a long story with an uninteresting ending), I will explain how to use an SSIS expression to parse a server and database name out of a connection string stored in an SSIS variable. This is useful when the connection string variable is being passed in at run time and you’d like to record it in your audit tables (or something else of that nature).

The starting assumption is that you have a connection string stored in a variable. Something like this:

To make it a bit easier to read, the value we are parsing is as follows:

Data Source=DevServer1;Initial Catalog=DevDatabaseA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

In my case the two fields I’m interested in are the DataSource and the InitialCatalog. Since, connection strings are typically written in the same order I thought about using the Token function in SSIS to get the first two values based on the “;” delimiter, but then I got paranoid someone would create a connection string in a different order and everything would fall to pieces.

So instead I went with a slightly more robust solution, combining, the SUBSTRING, FINDSTRING, and TOKEN functions in a single expression.

TOKEN(substring(@[User::SourceDB_ConnectionString],findstring(@[User::SourceDB_ConnectionString],”Data Source=”,1)+12, 1000), “;”, 1)

The basic gist of what’s happening in the function is as follows:

1. Use FINDSTRING to determine the position of the term “DataSource=”. Since FINDSTRING returns the position of the first letter of the string, add 12 to that number (“DataSource=” has 12 characters).
2. Use the number from FINDSTRING as the starting position of the SUBSTRING function. Set the substring length to 1,000 as a shortcut to actually finding the length of the string.
3. Use the TOKEN function to find the semicolon which indicates the end of the value for “DataSource=”.

In my specific case what I was looking for was the server name and database name from the connection string, with the goal on concatenating them together in the typical “ServerName.DatabaseName” format. Using the same logic as above I crafted the following SSIS expression to give me exactly what I was looking for.

TOKEN(substring(@[User::SourceDB_ConnectionString],findstring(@[User::SourceDB_ConnectionString],”Data Source=”,1)+12, 1000), “;”, 1)+”.”TOKEN(substring(@[User::SourceDB_ConnectionString],findstring(@[User::SourceDB_ConnectionString],”InitialCatalog=”,1)+16, 1000), “;”, 1)

Brilliant!