THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Verify a connection before using it [SSIS]

Just recently I've inherited some SSIS packages that were in dire need of fixing however, as is often the case, most of my battles were with connection string configurations.

It always baffles me when I see packages that don't log information that would be useful for debugging purposes and when its me that has to debug those packages I tend to get a little irate. Do a favour to yourself and the poor soul that inherits your packages by placing a Script Task at the start of your package with the following code in it:

        public void Main()
bool failure = false;
bool fireAgain = true;
foreach (var ConnMgr in Dts.Connections)
1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
ConnMgr.Name, ConnMgr.ConnectionString),
"", 0, ref fireAgain);
1, "", String.Format("Connection acquired successfully on '{0}'",
"", 0, ref fireAgain);
catch (Exception ex)
-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",
ConnMgr.Name, ex.Message),
"", 0);
= true;
if (failure)
= (int)ScriptResults.Failure;
= (int)ScriptResults.Success;

You'll be glad that you did because you'll get your connection strings appearing in your log file:

SCR Output Connection Strings: ConnectionManager='DB', ConnectionString='Data Source=dev;Initial Catalog=AdventureWorks;Integrated Security=SSPI;'
SCR Output Connection Strings: Connection acquired successfully on 'DB'


P.S. Those of you that have been following my blog long enough may know that I posted this back in 2005 however I don't think there's any harm in putting it out there again, especially given that:

  1. more people are now using SSIS
  2. The previous code was
  3. In the previous post the code was in a JPEG thus not copy/paste-able (god only knows why I did that)
Published Tuesday, October 25, 2011 4:35 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Michael said:

Thanks, it's very useful

October 26, 2011 3:25 AM

Ramdas said:

Thank You , really a useful script.

November 2, 2011 2:28 PM

Patrick said:


Thanks very much for posting

March 28, 2012 6:37 AM

Irving Gallegos said:


I´m triying to use the code below but i have an issue.

the reference to Microsoft.Sql.MannagedDts.Runtime doesn't contains a definition for Dts.Events.FireInformation.

i aggregate the reference from this file :C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\

In advance thanks!

Sorry for my bad english.

June 22, 2012 7:44 PM

BlBrothers said:

Thanks so much! Really very useful!!!

December 11, 2012 10:05 AM

phokaia said:

Thanks for the script. Perfect...

November 11, 2013 12:35 AM

Manish said:

Really Helpfull nice script to validate connection

November 11, 2013 8:03 AM

Nick said:

Cheers pal. Saved me a lot of bother here.

November 26, 2013 10:25 AM

Craig said:

Looks interesting. I don't know much about scripts, but how can this be changed to send an e-mail if the connection fails?

ANy help is appreciated.

June 10, 2014 5:34 PM

Gary said:


I have an initiation package that checks everything before we getting going with the ETL process. I wanted to check all connections and finding this page was a gem! Already thought of and coded for me, great!

However I've observed and an un-intentional behaviour. When the aquireconnections runs against and excel connection and the excel file doesn't exist (because I deliberately deleted). The aquireconnections seems to helpfully create the file for me. So the validation process doesn't return an error and my ETL starts with an empty excel spreadsheet :(

November 19, 2014 6:50 AM

Stuart said:

Hi, very useful. do you know whether the AcquireConnection can also be reliably used for a SMTP connection?  Or is there a workaround to using AcquireConnection for SMTP?

Thanks in advance

January 28, 2015 10:57 AM

Brennan said:

Perfect.  Exactly what I was looking for.  Thank you very much.

October 29, 2015 9:50 AM

Karthik said:

Please post the link for script

May 31, 2016 5:44 AM

Gihan said:

How to get MySQL ADO.NET connection's connection string which should be able to store in project parameter.

May 31, 2017 1:10 AM

Leave a Comment


This Blog


Privacy Statement