THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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)
{
Dts.Events.FireInformation(
1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
ConnMgr.Name, ConnMgr.ConnectionString),
"", 0, ref fireAgain);
try
{
ConnMgr.AcquireConnection(
null);
Dts.Events.FireInformation(
1, "", String.Format("Connection acquired successfully on '{0}'",
ConnMgr.Name),
"", 0, ref fireAgain);
}
catch (Exception ex)
{
Dts.Events.FireError(
-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",
ConnMgr.Name, ex.Message),
"", 0);
failure
= true;
}
}
if (failure)
Dts.TaskResult
= (int)ScriptResults.Failure;
else
Dts.TaskResult
= (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'

@jamiet

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 VB.net
  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

Comments

 

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:

Fantastic!

Thanks very much for posting

March 28, 2012 6:37 AM
 

Irving Gallegos said:

Hello.

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\10.0.0.0__89845dcd8080cc91

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement