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:
- more people are now using SSIS
- The previous code was VB.net
- In the previous post the code was in a JPEG thus not copy/paste-able (god only knows why I did that)