Sometimes, in a script task, you might need to make a connection out to do work. If you are using SMO, it's a smart thing to reuse the metadata from an existing connection manager. This starts off something like this:
ConnectionManager cm = Dts.Connection["MyConnectionName"];
But then how do you get the server and database names out of the connection manager so you can use the strings to create Server and Database objects? You might think it would be in the properties of the ConnectionManager object, but not so lucky. You actually have to parse it out with something ugly like this:
string srv = cm.Properties["ServerName"].GetValue(cm).ToString();
string db = cm.Properties["InitialCatalog"].GetValue(cm).ToString();
I don't know about you, but I will never remember that kookiness and I will always be google/guessing through a lot of trial and error to get it right. But I stumbled upon this Class today and I think it can simplify this mess: System.Data.SqlClient.SqlConnectionStringBuilder (other connection types are available as well, i.e. System.Data.Odbc.OdbcConnectionStringBuilder, System.Data.OleDb.OleDbConnectionStringBuilder)
This stringbuilder will allow you to create an object in which you can easily access the properties of the connection string. So the new version looks like this:
//using the connection manager from above
var connStr = new SqlConnectionStringBuilder(cm.ConnectionString);
string srv = connStr.DataSource;
string db = connStr.InitialCatalog;
And that is something even I can remember :)Labels: Script Task, SSIS