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.
