CODEWITHSUNDEEP
sql-serverforeachssisvisual-studio-2005
Jamie Stuart Robin Parsons
Jamie Stuart Robin Parsons

Reputation: 1152

SSIS Foreach Loop with visual feedback

I have a foreach loop container in my SSIS package which iterates about 30 .mdb (Access 2000) files.

These files can be quite large (up to 1GB), so I would like some sort of 'visual feedback' to show which file is currently being iterated, either as a file name or as a 'currently on x out of y files'.

Is there any way to dynamically show this during execution of a package? Pehaps dynamically updating the name of a box?

I am using SQL Server / Visual Studio 2005

Thanks in advance.

Upvotes: 2

Views: 589

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

Put a script task at the beginning inside your foreach loop, select Visual Basic, select which variables you want to monitor as ReadOnlyVariables, then replace the default Public Sub Main() with this code (edit: also included Enum ScriptResults code per request): 

Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
    Dim msg As String = ""
    For Each Variable As Microsoft.SqlServer.Dts.Runtime.Variable In Dts.Variables
        msg = msg & "--" & Variable.Name & "|| " & Variable.Value.ToString() & "||"
    Next

    Dts.Events.FireInformation(-1, "", msg, String.Empty, -1, False)
    Dts.TaskResult = ScriptResults.Success
End Sub

The progress tab will then log the variables at the start of each iteration.

Upvotes: 3

Related Questions