If you some of the dynamic features of SSIS such as package configurations or property expressions then sometimes trying to work out were your connections are pointing can be a bit confusing. You will work out in the end but it can be useful to explicitly log this information so that when things go wrong you can just review the logs. You may wish to develop this idea further and encapsulate such logging into a custom task, but for now lets keep it simple and use the Script Task. The Script Task code below will raise an Information event showing the name and connection string for a connection. Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim fireAgain As Boolean
' Get the connection string, we need to know the name of the connection
Dim connectionName As String = "My OLE-DB Connection"
Dim connectionString As String = Dts.Connections(connectionName).ConnectionString
' Format the message and log it via an information event
Dim message As String = String.Format("Connection ""{0}"" has a connection string of ""{1}"".", _
connectionName, connectionString)
Dts.Events.FireInformation(0, "Information", message, Nothing, 0, fireAgain)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Building on that example it is probably more flexible to log all connections in a package as shown in the next example.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim fireAgain As Boolean
' Loop through all connections in the package
For Each connection As ConnectionManager In Dts.Connections
' Get the connection string and log it via an information event
Dim message As String = String.Format("Connection ""{0}"" has a connection string of ""{1}"".", _
connection.Name, connection.ConnectionString)
Dts.Events.FireInformation(0, "Information", message, Nothing, 0, fireAgain)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
By using the Information event it makes it readily available in the designer, for example the Visual Studio Output window (Ctrl+Alt+O) or the package designer Execution Results tab, and also allows you to readily control the logging by choosing which events to log in the normal way.
Now before somebody starts commenting that this is a security risk, I would like to highlight good practice for building connection managers. Firstly the Password property, or any other similar sensitive property is always defined as write-only, and secondly the connection string property only uses the public properties to assemble the connection string value when requested. In other words the connection string will never contain the password. I have seen a couple of cases where this is not true, but that was just bad development by third-parties, you won’t find anything like that in the box from Microsoft.
Whilst writing this code it made me wish that there was a custom log entry that you could just turn on that did this for you, but alas connection managers do not even seem to support custom events. It did however remind me of a very useful event that is often overlooked and fits rather well alongside connection string logging, the Execute SQL Task’s custom ExecuteSQLExecutingQuery event. To quote the help reference Custom Messages for Logging -
Provides information about the execution phases of the SQL statement. Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. The log entry for the prepare phase includes the SQL statement that the task uses.
It is the last part that is so useful, how often have you used an expression to derive a SQL statement and you want to log that to make sure the correct SQL is being returned? You need to turn it one, by default no custom log events are captured, but I’ll refer you to a walkthrough on setting up the logging for ExecuteSQLExecutingQuery by Jamie.
