Currently, we have this VB script in SSIS on a ForEach Loop:

    Public Sub Main()
        Dim url, destination As String
        destination = Dts.Variables("DestinationPath").Value.ToString   Dts.Variables("NomFichier").Value.ToString
        url = "http://XXXXXX/ReportServer_RS/Pages/ReportViewer.aspx?XXXXXX&rs:Command=Render&P_CodeAffilie="   Dts.Variables("CodeAffilie").Value.ToString   "&P_NumeroMouvement="   Dts.Variables("NumeroMouvement").Value.ToString   "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub

But when it bugs, we can't find the reason of the error. So, I thought of adding a code that generate a log file. Into this log file it will store the script before the execution. Like that, when it will bug, I can open the log file and see at which step it has bugged. How can I do that please? Thanks.

CodePudding user response：

I would encourage you to use the native logging mechanics of SSIS which is raising events. The runner/invoker of packages can then decide what, how, and where it logs the data. By default an Information event is perfect for this task.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.idtsinfoevents.fireinformation?view=sqlserver-2019

Apologies on probably incorrect VB.NET It has been a while and I am freehanding this code.

Public Sub Main()
    Dim url, destination As String
    Dim errTemplate As String = "{0} -> {1}"
    Dim fireAgain As Boolean = False

    ' Not sure on the syntax here, too lazy to google
    ' Enumerate through the Variables collection
    ' Raise information event for each variable found
    For Each item As Variable in Dts.Variables
        Dts.Events.FireInformation(0, "SCR SSRS thing", String.Format(errTemplate, item.QualifiedName, item.Value), "", 0, ByRef fireAgain)
    Next

    destination = Dts.Variables("DestinationPath").Value.ToString   Dts.Variables("NomFichier").Value.ToString

    url = "http://XXXXXX/ReportServer_RS/Pages/ReportViewer.aspx?XXXXXX&rs:Command=Render&P_CodeAffilie="   Dts.Variables("CodeAffilie").Value.ToString   "&P_NumeroMouvement="   Dts.Variables("NumeroMouvement").Value.ToString   "&rs:Format=EXCEL"

    ' Log the destination 
    Dts.Events.FireInformation(0, "SCR SSRS thing", String.Format(errTemplate, "destination", destination), "", 0, ByRef fireAgain)
    ' Log the url 
    Dts.Events.FireInformation(0, "SCR SSRS thing", String.Format(errTemplate, "url", url), "", 0, ByRef fireAgain)

    ' We know this might blow up, so put some error handling

    try
        SaveFile(url, destination)
    catch ex As Exception
        ' TODO better error handling
        Dts.Events.FireInformation(0, "SCR SSRS thing", String.Format(errTemplate, "Exception thrown", ex), "", 0, ByRef fireAgain)
        Dts.Events.FireInformation(0, "SCR SSRS thing", String.Format(errTemplate, "Exception thrown", ex.InnerException), "", 0, ByRef fireAgain)
    end try
    Dts.TaskResult = ScriptResults.Success
End Sub

Assuming that all worked, every time the Script Task runs, it's going to dump the values of all the SSIS Variables that have been passed in as read or write values as well as dumps the local variables url and destination.

Personally, I like that level of information being generated on successful and non-successful runs as I can compare what the previous loop looked like and compare it to the failing one.

But, if you find that's too much diagnostic noise, move all the FireInformation events into the error handler (Catch block) and when it goes boom, you'll get a lovely set of recorded values to debug against.