Tech Off Post

Single Post Permalink

View Thread: vb.net & SSIS package execution
  • User profile image
    Lunchy

    When you're in the design view of your SSIS package you can right click in the designer window and click the "Variables" menu item.  This should display the "Variables" tab where you can add variables to the DTS package.  I will use the example of 'vDivisionID' which I have set to be an Int32

    Then you can go into the properties of the Script Task that you are working with, and you'll see "ReadOnlyVariables" and a "ReadWriteVariables" properites.  You will need to enter a comma seperated list of the SSIS variables you want to access from the script in which ever of these two properties is appropriate.  In my case I put 'vDivisionID' into the "ReadOnlyVariables" property.

    Then, once you're in the script editor ready to write the vb.net code, you can access these varaibles with the Dts.VariableDispenser class like this...

        Public Sub Main()
            Dim variables As Variables
            Dim DivisionID As String

            If Dts.VariableDispenser.Contains("vDivisionID") = True Then
                DivisionID = Dts.Variables("vDivisionID").Value.ToString
            Else
                Dts.TaskResult = Dts.Results.Failure
                Exit Sub
            End If

            Dts.TaskResult = Dts.Results.Success
        End Sub

    I hope this helps and makes sense, I was in a rush while writing this. Smiley