記錄連接字串

Logging
connection strings

by Darren Green 10
Jun 2009 21:44

 

fom： http://www.sqlis.com/post/Logging-connection-strings.aspx 

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 SystemImports Microsoft.SqlServer.Dts.RuntimePublic 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 SubEnd Class

Building on that example it is probably more flexible to log all connections
in a package as shown in the next example.

Imports SystemImports Microsoft.SqlServer.Dts.RuntimePublic 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 SubEnd 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.