Verify a connection before using it [SSIS]

jamiet — Tue, 25 Oct 2011 14:35:00 GMT

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)

Lock variables in Script Task using C# [SSIS]

jamiet — Thu, 29 Jul 2010 12:09:00 GMT

If you have ever written code to lock variables inside a SSIS Script Task or Script Component then you will probably have written some VB.Net code that looks something like this:



    Public Sub Main()
        Dim vars As Variables
        Dim fireAgain As Boolean = True
        Dts.VariableDispenser.LockOneForRead("VarName", vars)
        'Do something with the value....
        vars.Unlock()
        Dts.TaskResult = ScriptResults.Success
    End Sub

If you have then moved to SSIS2008 then you may want to do similar in C# so you would convert this code to be something like:

public void Main() {
    Variables vars;
    bool fireAgain = true;
    Dts.VariableDispenser.LockOneForRead("varName", ref vars);
    //Do something with the value...
    vars.Unlock();
    Dts.TaskResult = (int)ScriptResults.Success;
}

However, doing so will result in an error:

Use of unassigned local variable 'vars'

What gives? Are you telling me the same logic doesn't work in C#? Actually the solution is pretty simple. Due to the way that C# and VB initialise their variables differently you simply have to set your variable to NULL as follows:

public void Main() {
    Variables vars = null ;
    bool fireAgain = true;
    Dts.VariableDispenser.LockOneForRead("varName", ref vars);
    //Do something with the value...
    vars.Unlock();
    Dts.TaskResult = (int)ScriptResults.Success;
}

Easy when you know how!!!

@Jamiet

SSIS Junkie : C#

Verify a connection before using it [SSIS]

Lock variables in Script Task using C# [SSIS]