artifactual

For whatever reason, the windows explorer “Search…” does not work when I’m searching the .dtsConfig files on my development laptop. It will routinely return “no results” found when I KNOW there are files containing the text I am searching for. Since I use the .dtsConfig files to contain connection strings, I typically am searching to make sure I am not connecting to the test server I think I am.

Luckily, this led me to discover a new favorite utility in Visual Studio the rest of the world already knew about - Find and Replace. From the VS.NET menu: Edit >> Find and Replace >> Find in Files (or [Ctrl]+[Shift]+F for keyboard jockeys)

you can launch “Find and Replace”

which made me happy enough for accurately searching the .dtsConfig files (as opposed to Windows Explorer’s Search…) But what my new favorite VS.NET feature, Folder Sets. By clicking the browse ellipses next to the “Look in”drop down, I can set the search folders with the “Choose Search Folders” dialog:

where I can set (and save) a batch of folders I want the Find and Replace utility to search. So simple, I see myself using this a lot in the future.

In this post at SSIS Junkie, I saw one complaint regarding how to evaluate dynamically generated SQL statements. There, Jamie suggests using the watch windows in the debugger. This is a great tip for development, but I also like to track SQL in the proverbial “wild”.

Here is how I log dynamically generated SQL statements, built using the Script component within SSIS. This is based on an example I saw online somewhere - but I can no longer remember where to give proper credit.

Write a Script component that contains code similar to this:

Public Sub Main()
Dim DynamicSQLStatement As String
Dim emptyBytes(0) As Byte
Try
' Build Dynamic SQL Statement
DynamicSQLStatement = "SELECT MyColumn FROM MyTable"
' Generate Log Statement
Dts.Log("Dynamic SQL Generated: " & DynamicSQLStatement, 0, emptyBytes)
' Return the dyanmic SQL for user in OLE Source component
Dts.Variables("DynamicSQLStatement").Value = DynamicSQLStatement
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub

Enable the “ScriptTaskLogEntry” for this component. Note: I already have logging enable at the package level, but I override it for the script component to gain access to the “Script Task Log Entry”. (click image to enlarge)

In this case, I have SQL Server logging configured, so the log entry appears in the table dbo.sysdtslog90.

This is incorporated into a larger logging & auditing system (largely based on the Kimball method presented by Joy Mundy here), so that I can trace what SQL was used by a particular ETL batch.