SQL Server Performance

parent chiled package

Discussion in 'SQL Server 2005 Integration Services' started by tboonleong, Jan 7, 2007.

  1. tboonleong Member

    May I know how to do the parent package call childreen packages and sub chiren pakages for MS sql Intergration Services 2005.

    In SQL2000, it will simple using a paackage tool.But in SQL 2005 I had no ildea about this.

    Hope anybody who know about this, please advice.thanks.
  2. ranjitjain New Member

    In parent package,
    include Execute Package Task and define connection details of package you want to execute which will act as child package.
  3. tboonleong Member

    Thanks for your reply.
    Can the variables at the master package be passed into the children package and sub children package.
    If yes then how does it do be done? Which steps are required.
    Thanks.
  4. ranjitjain New Member

    FROM 2005 BOL:

    Passing Values to Child Packages
    Frequently a child package uses values passed to it by another package that calls it, ordinarily its parent package. Using values from a parent package is useful in scenarios such as the following:

    Parts of a larger workflow are assigned to different packages. For example, one package downloads data on a nightly basis, summarizes the data, assigns summary data values to variables, and then passes the values to another package for additional processing of the data.

    The parent package dynamically coordinates tasks in a child package. For example, the parent package determines the number of days in a current month and assigns the number to a variable, and the child package performs a task that number of times.

    A child package requires access to data that is dynamically derived by the parent package. For example, the parent package extracts data from a table and loads the rowset into a variable, and the child package performs additional operations on the data.

    You can use package configurations to make values from the parent package available to the child package. Integration Services provides a configuration type, the Parent Package Variable configuration, for passing values from parent to child packages. The configuration is built on the child package and uses a variable in the parent package.

    The parent package variable can be defined in the scope of the Execute Package task or in a parent container such as the package. If multiple variables with the same name are available, the variable defined in the scope of the Execute Package task is used, or the variable that is closest in scope to the task. The configuration is then mapped to a variable in the child package, or to the property of an object in the child package. The variable can also be used in the scripts used by the ActiveX Script task, Script task, or Script component.

    For more information, see Integration Services Variables, Using Variables in Packages, and Creating Package Configurations in BOL.

    As 2005 has undergone many changes from Integration services viewpoint, it would be better if you spend more time reading BOL and ask specific questions/problems here.
  5. tboonleong Member

    The actual scenario was as below:

    I had created a package called package1.dtsx (parent package) and a variable called Var1 to be created as well.
    Later I created a packge called package2.dtsx (children package).
    Inside the package1.dtsx 's control flow I created a Execute Package task for package2.dtsx . In the Execute Package task editor, I configured the setting as below:

    Location : File System
    Connection : Package2.dtsx

    When I go back to Package2.dtsx(children package) to check the varaible window, I did not see the user definited variable for Var1 which had been created at package1.dtsx (parent package).

    What steps and configuration that I miss out for able the Package2.dtsx(children package) use the variable passed from Package1.dtsx(parent package)?

    Thanks again for your help.


  6. ranjitjain New Member

    Hi,
    the variable created in master package will not be visible in package2 design mode but will be only accesible during run time when you refer to that.

    To test whetherr var1 is accessible during run time try this:
    In child package create a script task and add master package variable var1 in read only variable property.
    Then in designing of that script access var1 in following way:

    Public Sub Main()
    Dim childvar1 As String = Dts.Variables("var1").Value.ToString
    MsgBox(childvar1)
    Dts.TaskResult = Dts.Results.Success
    End Sub


    Save and build solution. Now execute master package, you should see the var1 being accessed in child package and also displayed in msgbox.
    Hope this helps

Share This Page