none
Copy variables from 1 package to another

    Question

  • I have to use the same set of 20-30 variables in my 8 -9 packages thats I woudl be building. Can I copy the variables I create in 1 package to another. I can copy components, but I am not aware if I can copy if yes please let me know how..

    Thanks!

    Thursday, June 10, 2010 2:43 AM

Answers

  • It was a good idea but only if I had to use variables before .. Now my packages are build but I need to set a lot of variables in all packages :)

    Hope it could be done would make my life much easier so I could write the business logic rather than just doing redundant task

     

     

    Now the only option left to u my friend is the adding of the variables programmatically :) 

    since only 8 9 packages are there open package in C# and add the set of variables to package save them again .. to some location .. 

    Create new SSIS project and copy paste these newly {programatically } generated packages into project .... 

     

    hope that helps 

    Thursday, June 10, 2010 7:55 AM
  • Other work around [which i would not recommend though/ be very careful]

    1. open ur package [which has all the variables that u need to copy] with text editor OR right click package in BIDS and click view code.

    2. now copy all the variable definitions from [original package code] which would be something like below.

    <DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">aa</DTS:Property><DTS:Property DTS:Name="DTSID">{8C02656F-F21B-46AA-A2D5-83C10ED5011D}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
    

    Just copy paste <DTS:Variable>..........TO ................ </DTS:Variable>  of all the variables that u need . 

    3. open ur 8 ,9 packages in similar fashion. one by one and paste point 2. definitions into the source code of these new packages and do "Save AS" newPackageNAME.dtsx. 

    do this for all ur packages open them in BIDS. and u are good to go :) expressions n all u can do manually via BIDS later :) 

    Hope that helps.. 

    kunal 

     

     

     

     

     

    Thursday, June 10, 2010 8:05 AM
  • Adding the variables in code would be quite easy, and slightly less risky that the XML approach, although that may be quicker. If you want a quick start try the code here which will help you all the lookup over files and loading packages. Just change it to look for the package.variables collection instead of at the tasks. If you need more info just ask.

    In future you may find using package templates a useful feature - http://support.microsoft.com/kb/908018


    http://www.sqlis.com | http://www.konesans.com
    Thursday, June 10, 2010 10:57 AM
    Moderator

All replies

  • I  don't think so you can copy the variables.

    you can make a copy of package and then delete the unwanted tasks , so that's how you would have all the variables.

    Thanks

    Thursday, June 10, 2010 2:46 AM
  • It was a good idea but only if I had to use variables before .. Now my packages are build but I need to set a lot of variables in all packages :)

    Hope it could be done would make my life much easier so I could write the business logic rather than just doing redundant task

     

     

    Thursday, June 10, 2010 2:55 AM
  • It was a good idea but only if I had to use variables before .. Now my packages are build but I need to set a lot of variables in all packages :)

    Hope it could be done would make my life much easier so I could write the business logic rather than just doing redundant task

     

     

    Now the only option left to u my friend is the adding of the variables programmatically :) 

    since only 8 9 packages are there open package in C# and add the set of variables to package save them again .. to some location .. 

    Create new SSIS project and copy paste these newly {programatically } generated packages into project .... 

     

    hope that helps 

    Thursday, June 10, 2010 7:55 AM
  • Other work around [which i would not recommend though/ be very careful]

    1. open ur package [which has all the variables that u need to copy] with text editor OR right click package in BIDS and click view code.

    2. now copy all the variable definitions from [original package code] which would be something like below.

    <DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">aa</DTS:Property><DTS:Property DTS:Name="DTSID">{8C02656F-F21B-46AA-A2D5-83C10ED5011D}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
    

    Just copy paste <DTS:Variable>..........TO ................ </DTS:Variable>  of all the variables that u need . 

    3. open ur 8 ,9 packages in similar fashion. one by one and paste point 2. definitions into the source code of these new packages and do "Save AS" newPackageNAME.dtsx. 

    do this for all ur packages open them in BIDS. and u are good to go :) expressions n all u can do manually via BIDS later :) 

    Hope that helps.. 

    kunal 

     

     

     

     

     

    Thursday, June 10, 2010 8:05 AM
  • Adding the variables in code would be quite easy, and slightly less risky that the XML approach, although that may be quicker. If you want a quick start try the code here which will help you all the lookup over files and loading packages. Just change it to look for the package.variables collection instead of at the tasks. If you need more info just ask.

    In future you may find using package templates a useful feature - http://support.microsoft.com/kb/908018


    http://www.sqlis.com | http://www.konesans.com
    Thursday, June 10, 2010 10:57 AM
    Moderator
  • It would be a better approach to create a SSIS template and use it for the new packages. You could have the common variables, Connections and tasks in the template and reuse it.
    Sudeep's Domain
    Thursday, June 10, 2010 1:09 PM