Creating Dynamic Connection Strings in SSIS

Today I was enhancing the DBA Repository project to allow it to gather information on SQL Servers in another domain.  Since I am unable to authenticate my windows login from my primary domain to the one I need to gather information from I needed to find a way to get that data into my central repository using a SQL login.  I wanted to have a solution that was easy to maintain in a single location.

In a previous entry, DBA Repository – Create the Server List, I demonstrated looping through a collection of server names and changing the connection string’s SourceServer property to gather data from numerous servers.  The trick now is, not only is the server name variable but the authentication type must change appropriately as well if the server is no longer in the same domain.

In this example, I only have a single server in the domain and I know it will always be named the same so I look for that particular server name in my server collection.  However, this could simply be modified to use configuration from the dbo.Servers table to determine the domain the server resides in to determine the login type required.

To solve the challenge I created a SQL Login with all required permissions on my database server in the foreign domain.  Then in my SSIS’s connection manager I built the ConnectionString property based on an expression, I’m using the SQL Native Client 10 provider so my connection string may have different keywords than if another provider was used.  Also, you may be unfamiliar with the the conditional operator for SSIS expressions; it is just like the immediate if (IIf) used in SSRS but has this syntax: «boolean_expression» ? «when_true» : «when_false».

Here’s the expression:

FINDSTRING(@[User::SourceServer], "REMOTESERVERNAME", 1) > 0
? "Data Source=" + @[User::SourceServer] + ";User ID=MyDBARepositorySQLLogin;Password=MyDBARepositorySQLLoginPassword;Initial Catalog=master;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS-DBARepository_FindOrphanedUsers;Auto Translate=False;"
: "Data Source=" + @[User::SourceServer] + ";Initial Catalog=master;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DBARepository_FindOrphanedUsers;"

Building the whole connection string as an expression rather than just a single property provides me more flexibility to control not only the server name but the authentication type dynamically.

Leave a Reply

Your email address will not be published. Required fields are marked *