2

At my work we have three different environments: dev, test, and prod. We also quite a few different SSIS packages. Currently, when we need to deploy to a particular environment, we update the connection for the package in Visual Studios and deploy it to the correct environment. I'd like to make this easier. I'd like to pass in the environment name, the server name, and the database name and have a parameterized connection string in order to run the package correctly.

I'm currently not allowed to set up an Integration Services Catalog for these environments and variables. The only options I have is to somehow pass in the parameters when I set up the SQL Server Agent Job that will execute these packages on a schedule.

I previously attempted using a parameter in the SSIS package, but when I attempted to pass in a value I got an error stating that parameters are read only.

So, how can I set up these packages to send the pertinent data they need for the server they're running on?

Clarification: We are currently deploying our packages by uploading them to MSDB.

  • Parameters are read only. So if you need to manipulate them, you'll need to copy them down to local variables using expressions. – sam yi May 22 '17 at 0:52
  • Have you tried storing these environment settings in the database. Custom configuration table. It would be a good alternative since you can't use catalog and environment variables. – sam yi May 22 '17 at 0:53
  • Config xml is also an option but I prefer to keep them all within the database. I find it cleaner. – sam yi May 22 '17 at 0:56
2

A common approach is to use a different SSIS configuration file for each environment.

Give it the same name, but different contents (server name, local paths, any other variables) in each environment.

  • I'm not quite sure how this works when uploading packages to sql server and scheduling them with sql agent jobs. could you add more detail to your answer? – DForck42 May 19 '17 at 13:40
  • @DForck42 I'm making some assumptions: You have 3 different SQL servers, right, one each for dev/test/prod? And you are putting the SSIS packages into a folder on the local drive of each SQL server? If so, just put the config file into the same drive/folder as the SSIS packages, with a different config file for each environment. Then make a job step of type "SSIS Package", you can specify the path to the package, and on the "configurations" tab, specify the path to the config file. – BradC May 19 '17 at 14:01
  • @DForck42 Or you can hard-code the config file path and name into each package, then just make sure the 3 config files have the same name but different contents for each environment. – BradC May 19 '17 at 14:13
  • we're deploying the packages by uploading them to SSIS via MSDB – DForck42 May 19 '17 at 15:42
  • @DForck42 Sorry, I'm not too familiar with how it works in that scenario, then. The "files in a folder" has always worked well for me. Even if the SSIS package is stored internally, isn't there still a "configuration" tab inside the Job step? – BradC May 19 '17 at 15:56
0

The parameters you provide aren't something you "use" in the package; they change the starting value of the property you're trying to update.

Some properties may really not let you directly set them directly. If that appears to be the problem, then see if you can set the property to an expression within the package itself. If so, make that expression an SSIS variable, and set the value of that variable when you run the package.

(Note that, if you've got a large number of items to configure, and you can't use a database, a configuration XML file (as suggested by BradC) would be easier to manage than trying to set dozens of values on the command line or in a SQL job.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.