SSIS Foreach Loop with visual feedback

Question

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.

mr.Reband · Accepted Answer · 2013-09-10 15:58:04Z

3

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.

edited Sep 10, 2013 at 15:58

answered Sep 10, 2013 at 14:19

mr.Reband

2,4312 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jamie Stuart Robin Parsons Over a year ago

Thanks for the reply, i'm getting a few errors however: Variable.Value - Option Strict On Prohibits operands of type Object for '&'` ScriptResults - Name Script Results is not declared

mr.Reband Over a year ago

Updated my answer to get rid of that first error (.ToString()); the second error suggests that you also deleted the Enum ScriptResults which is in the code by default -- is that the case?

Jamie Stuart Robin Parsons Over a year ago

The install was managed by a separate team so I can only assume. The final SSIS package will be uploaded to a server where I don't have admin rights so I won't be able to add any additional files. Any way to add the `Enum ScriptResults' via a class in the script?

mr.Reband Over a year ago

Updated my answer to include the enum code. It should all go in the same script task.

Collectives™ on Stack Overflow

SSIS Foreach Loop with visual feedback

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related