Solved

Adding configuration file to SSIS package

Posted on 2014-02-11
7
476 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39851530
Yes, you can definelty add the configuration and schedule the same using the SQL Agent Jobs...

Please use the below tutorial to do the same

http://technet.microsoft.com/en-us/library/ms138023(v=sql.100).aspx
0
 
LVL 3

Expert Comment

by:oromm
ID: 39851569
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.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39853237
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?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
ID: 39853356
0
 
LVL 3

Expert Comment

by:oromm
ID: 39853914
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/implementation 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.
0
 
LVL 35

Accepted Solution

by:
YZlat earned 0 total points
ID: 39856156
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39866885
I used my own link in providing the solution
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question