0

Having now added a like for like DB to do UAT on to our server I have run into a problem with our SSIS Packages. They are stored as files and as such we use File System in SQL Server Agent to run them on a schedule. I now need to run these packages on both databases (different data sources).

I have checked over documentation and forums and when paramterizing over environments every time it relates to the deployment of packages to the catalog. Which I think is because with files they are saved with package data sources not project data sources.

This left me wondering...

Is it possible to switch data sources when calling a package using File System in SQL Server Agent?

Will
  • 228
  • 1
  • 2
  • 15
  • 2
    You can define a connection manager's connection string with expressions, yes, and those expressions can include both variables and parameters. Then you can pass a parameter to the package and the connection manager will use that parameter. It's been *a long* time since I used package deployment as the project method is *significantly* better, but I think they support parameters. In truth, however, this kind of thing is *far* easier with project deployed packages, where you use environment settings to define the server you are using. – Thom A Aug 26 '21 at 13:34
  • Naturally we want to avoid duplicate packages as maintaining it would be a nightmare and so it may be best for us to move away from files and move to the catalog. – Will Aug 26 '21 at 13:45
  • You can also modify the connection string in SQL Agent on the data sources tab where you configure the package. – Mark Wojciechowicz Aug 26 '21 at 16:37

1 Answers1

0

Unfortunately the answer on this is no.

Moving to the SSIS catalog allows you to switch between environments which you can set in SSMS.

This is a great link: https://blog.exsilio.com/all/configuring-environment-variables-ssis-package/

I did get an error when running my agent job after this but this was a need to set the runtime to 32bit.

In SSIS - Right click your solution and head to debugging to find the option. In SQL Server Agent - Head to Config and advanced where you would set the environment variable in the SSIS step you have created.

Will
  • 228
  • 1
  • 2
  • 15