CODEWITHSUNDEEP
sql-servert-sqldeploymentssispackage
John Smith
John Smith

Reputation: 187

SSIS - Package Configuration - Environment Variable

Few things I would like to clear before I ask my main question.

If you create more than 1 package (same source/destination), can you just use one Package Config file?

->Lets say I have created 10 packages using same source/destination. Can I create just one XML package configuration and use that xml file to edit path for source & destination? Or do I need to create 1 Package Configuration file per Package?

Let's say you have 50 packages that you are moving to production server from development server...Where can I store the package configuration files in this scenario?

->I have not created package configuration file for any package yet.

->Should I create a SINGLE XML package configuration file for ALL 50 packages?

->If not, what steps should I take?

->I read that Environment Variable is a useful in this situation.

->What steps do I need to take in order to create an Environment Variable? ELI5 if possible.

->I read that it only allows one property to be stored...do i have to create multiple Environment Variables for lets say Source, Destination, credentials, etc?

-> I read this tutorial on Evn Var but not understanding the procedure clearly and benefits of using Environment Variable for such situations (more than 1 packages, moving packages from one env to another, etc).

Thank you kindly for your help. Try not to down-vote this post as I have done research but just not understanding it.

Upvotes: 0

Views: 9613

Answers (1)

praveen
praveen

Reputation: 12281

1.Package Configuration file per Package?

You can reuse the same configuration file in multiple SSIS packages .
But you should make sure that you have the same name and the number of connection
manager and variables  defined in all the packages .If you have different names
and numbers   then you have to create different config files .Creating different
config files for each environment is a better option because you just need to change 
the connection string to point to a particular sql server instance but not suitable
for large number of ssis packages .

2.Environment Variable

In case you are going with Enviroment variable then you need to have same config
file for all the SSIS packages .Environment variable should be present in all your 
environments (test ,dev,prod).you need to have permission to create these variables.
Package Configuration file per Package.The problem with environment variable is it 
each variable can store only one property.So if more than one property needs to 
stored then you need to have different variables for it .

For more information please go thru this whitepaper

Upvotes: 1

Related Questions