It’s nearly a daily situation to use a single Integration Services Package on several servers. At least you should have a live system and a dev system.
If you modify the package and deploy it to your servers, there are several possible ways of modifying the data source, according to your environment.
- Local database: In case both servers have a local database you can run the package locally and use “localhost” as database server
- Shared data source: Very good solution for many situations, but not part of this article
- Command line parameters: flexible and dynamic solution
Step1: Create a variable “ServerName”
We create a new global (Scope = Package) variable called “ServerName”.
In this example I’ll use the server hostnames dbServerDev and dbServerLive so we use the DEV server as default value.
Step2: Create a Connection Manager
Create a new OLE DB Connection select your DEV server and your database.
Step3: Add the Variable as Expression to the Connection Manager
In the properties of the connection manager you can define expressions. These expressions can override the existing values. In this situation we define a new expression, select “ServerName” on the left side as property and the variable as expression.
Step4: Create your package steps
Let your package do something useful on the DEV DB.
Step5: Switch Database using Commandline Parameter
dtexec /file Package.dtsx /Set package.Variables[User::ServerName].Value;"dbServerLive"
You can also change parameters using the GUI for a new SQL agent job step as described in this post.