YZlat
asked on
Adding configuration file to SSIS package
I have an SSIS package with two connections in Connection manager. It reads data from one database and copies it into another. I would like to be able to dynamically specify source server, source database, target server, target database and other variables to make the package dynamic, since it will run in 3 different environments: Dev, QA, and Production. Thus all the server names will be different. Can someone guide me through adding a configuration file and making server names and database names dynamic?
Adding onto that...
Suggest on the package that reads/copies the data that you add a package configuration of type Parent Package Variable that depends on your connection string specifics to be provided. Then you can have multiple parent packages, one for each environment, that call into that first package passing in the appropriate source and destination information.
Suggest on the package that reads/copies the data that you add a package configuration of type Parent Package Variable that depends on your connection string specifics to be provided. Then you can have multiple parent packages, one for each environment, that call into that first package passing in the appropriate source and destination information.
ASKER
Surendra, actually my question was not how to run the package but how to add configuration to it.
oromm, I am new to SSIS and I am not sure I understand, could you please explain?
oromm, I am new to SSIS and I am not sure I understand, could you please explain?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The 2nd tutorial link sent by Surendra has a section about Parent Package Variable Configurations. From those tutorials, once you have a better understanding of the use/application/implementa tion of Package Configurations, you can configure your "working" package (i.e. the one that is doing the primary work to be accomplished) to pull the database connection string parameters from a parent/calling package.
You would then have a parent package for each of your environments with connection managers specific to those environments, or just variables with the connection string specifics. Each of those parent packages would have an "Execute Package Task" in the Control Flow to execute the same working package.
Essentially your working package is dependent upon the calling package to provide the connection information necessary. It is oblivious to anything about that calling/parent package, other than the fact that there are package variables that are providing values to your working package.
You would then have a parent package for each of your environments with connection managers specific to those environments, or just variables with the connection string specifics. Each of those parent packages would have an "Execute Package Task" in the Control Flow to execute the same working package.
Essentially your working package is dependent upon the calling package to provide the connection information necessary. It is oblivious to anything about that calling/parent package, other than the fact that there are package variables that are providing values to your working package.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used my own link in providing the solution
Please use the below tutorial to do the same
http://technet.microsoft.com/en-us/library/ms138023(v=sql.100).aspx