Old ladyHow Big Is Your Package?
Size up and convert your DTS package to SSIS. If you have the biggest DTS package, win a gift card and certificate of a world record package size.  
How to read/write variable value from child/parent package in SSIS? - Nayan Patel
in

Pragmatic Works

Nayan Patel

How to read/write variable value from child/parent package in SSIS?

Changing value of child package variable was easily possible in DTS using "Execute DTS Package Task" settings called inner/outer variables. But unfortunately this is not directly supported in SSIS if you using "Execute SSIS Package Task".
However you can use VB.net code inside script task to read/write child package variable. There is another way to read parent package variable in SSIS is "Configurations". The following two sections illustrate both techniques.
Read parent package variable from child package (Using Parent Variable Configuration)

The Parent Variable Configuration feature of SSIS allows variables from a parent package to be inherited by a child package. This is different than the SSIS’ predecessor, DTS, where variables were pushed down from the parent package to the child package. In SSIS the child package requests the variable by name from the parent package, allowing the variable to be inherited from any calling parent package that uses the Execute Package task to call the child package.
In the following screen value of parent variable varBatchID_Parent is being assigned to child package variable gvarBatchID_Child.

For more info please visit http://technet.microsoft.com/en-us/library/ms345179.aspx

DTS to SSIS Migrations/Conversion


You can select any available property as configuration target property including variables, connections, task or package property. In the following example target is value property of variable called gvarBatchID_Child


DTS to SSIS Migrations/Conversion


Read/write child package variable from parent package (Using Script Task)
Many times you have to pass variables or Static Values at run time to child package and due to complexity of logic you might want to use scripting instead of "Configuration" technique explained in the previous section. The following piece of code displays how you can load any SSIS package at run time, set package variables and execute the package.

Load Package, Set Variable and Execute Package at runtime

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()
        Dim oApp As Microsoft.SqlServer.Dts.Runtime.Application = New _
                                    Microsoft.SqlServer.Dts.Runtime.Application()
        Dim oPkg As Microsoft.SqlServer.Dts.Runtime.Package

        '//Load Child Package from SQL Server
        oPkg = oApp.LoadFromSqlServer("\DW_Packages\Task_ExecSQL", "(local)", "", "", Nothing)

        Dim vars As Variables

        '//If Child Package Variable Doesn't exist at runtime 
        '//then create a new variable and pass the value from Parent package variable.
        If oPkg.Variables.Contains("gvChild1") Then
            oPkg.VariableDispenser.LockOneForWrite("gvChild1", vars)
            Try
                vars("gvChild1").Value = "Hello!!! Set From Parent..."
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Else
            oPkg.Variables.Add("gvChild1", False, "", "Hello!!! Set From Parent...")
        End If

        oPkg.Execute() '//Execute the Child Package

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class
Published Nov 13 2008, 10:06 PM by Nayan Patel
Filed under: ,

Comments

No Comments

About Nayan Patel

Nayan Patel, MCSD, MCDBA, MCSE is a Product Manager at Pragmatic Works and also Architect/Lead Developer of DTS xChange and SSIS xPress products. He is a consultant, trainer, mentor and developer who is enthusiastic about developing robust BI Tools. He has expertise in many areas of Business Intelligence including Integration Services, Reporting Services, Database Administration, and .Net Software Development. Nayan has created BI and software solutions for Pragmatic Works. He has also worked on several projects related to Windows Applications, Web Applications and BI Applications using Microsoft technologies.
Copyright Pragmatic Works
Powered by Community Server (Non-Commercial Edition), by Telligent Systems