How to use the same variables in different packages?

  • I need to build 8-10 SSIS packages

    and I thought it would be a good idea to store all the

    data sources in a database.

    But the question is now:

    Is it possible to have several SSIS packages that call some other re-usable package and populate variables form there.

    I mean:

    package1 has var1,var2,var3,var4

    package2 has var1,var2,var3,var4

    Instead of assigning these variables in each package

    can I just call some external package 3 and populate

    package 1 and 2 variables?

    So in other words how to implement "include file" C# style functionality

    in SSIS?

  • You can create a package and then re-use it by using the Execute Package Task.

  • I know I can call another package but

    the external package 2 knows nothing about my variables in package 1.

    So how do I return values from package 2?

    My question is:

    "I have package 1,package 2, package 3,etc.

    All of them have the same set of variables where I store data sources.

    Instead of executing the same Sequence Container "Set Data Source"

    in each package how can I execute external package and populate my variables?"

    In other words, can I have a process that works like a Function in programming:

    1. Call function

    2. Return values and use them where you called function

  • U can use the variables of a different package in other packages

    have a master package which holds the values of connections and values

    Have all other packages as child packages with the same name

    the child package variable can be configured from master using the package configuration

    Master:: in package configurations..

    Since you have a single master package all the child packages will pick up this value.

    Change the namespace in master package from User to Master

    giving the namespace::variablename you should be able to use it across any package.

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • have a master package which holds the values of connections and values

    Have all other packages as child packages with the same name

    the child package variable can be configured from master using the package configuration

    Master:: in package configurations..

    Since you have a single master package all the child packages will pick up this value.

    Change the namespace in master package from User to Master

    giving the namespace::variablename you should be able to use it across any package.

    Questions:

    1. Where exactly and how you change package configuration

    2.

    Have all other packages as child packages with the same name

    How can you have identical package names in the same solution

    Could you be more specific and outline the steps.

  • hi this link gives the information on how to link parent variables with child variables

    http://www.sqlis.com/27.aspx

    and secondly i think there is typo in the reply

    Create all child packages with same variable names in a sense create a template package

    add variables to the package

    add configurations to the package

    use this package to create child package using the step add existing package so that you need not configure all the child packages.

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • I'm not sure why you want to use another package to populate the variables.

    Most of the packages I build have as one of the first steps a task that initializes variables from a database table. Wouldn't that work?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin,

    I thought about calling the same INITIALIZATION

    step in all packages. But in my case I would need to have

    a sequence container with 5-6

    Execute SQL tasks that populate

    variables. That seems to be like repeating 6 processes

    again and again. I thought it would be cleaner

    just calling one external package.

    Plus I want to learn how to use Parent-Child packages architecture.

    Alvin,

    Is it possible to send me a sample of your package to:

    softlar@rogers.com

    I want to see how exactly you implement variables initialization

    from database.

  • Venkat,

    I went to

    http://www.sqlis.com/27.aspx

    and tried to follow their sample but it did not work for me.

    They say you can specify the scope as -call the called package-

    but Scope field can not be modified.

  • I tried this example:

    http://www.sqlis.com/27.aspx

    and got an error:

    SSIS package "Caller.dtsx" starting.

    Executing ExecutePackageTask: C:\SSIS\Master\Master\Called.dtsx

    Information: 0x40016042 at Called: The package is attempting to configure from the parent variable "MyExecutePackageVariable".

    Information: 0x40016042 at Called: The package is attempting to configure from the parent variable "MyExecutePackageVariable".

    Error: 0xC0012024 at Script Task: The task "Script Task" cannot run on this edition of Integration Services. It requires a higher level edition.

    I found this issue was reported already at:

    http://www.sqlservercentral.com/Forums/Topic279247-148-1.aspx#bm280035

    Any suggestions?

  • I installed Integrated Services on my local pc and the error is gone now.

    But I still don't understand from this example

    at http://www.sqlis.com/27.aspx

    this:

    How come that Script Task has

    MsgBox(vs("localVar").Value.ToString())

    but instead "MyExecutePackageVariable" variable value is showing up?

    I don't get it.

    Are all local variables in Child package replaced with variables

    from Parent package?

    What's going on here?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply