CODEWITHSUNDEEP
sql-serverexcelvariablesssisetl
Glen
Glen

Reputation: 11

SSIS Variable values per deployment configuration

I am using Visual Studio to create a very simple ETL package that reads some Excel file and uploads it into a database. We have a variable configured which contains the location of the source-file. Have have two environments: dev(evelopment) and prod(uction).

In location variable can either hold:

Ideally I would like to have this variable set by the deployment value (development / test).

I have looked into the deployment configuration, but I cannot find where I can set these variable values for different deployments.

Can anyone point me into the right direction?

Thanks!

Upvotes: 1

Views: 444

Answers (1)

userfl89
userfl89

Reputation: 4810

Use a Project Parameter to hold the location of the source file instead. An overview this process is below.

  • Open the Projects.params windows in the Solution Explorer just below the project name. Create a string parameter here to hold the source file location.
  • After this open Configuration Manager from the top menu in SSDT by going to Build > Configuration Manager. In here, create a new solution configuration for each environment, development and production, by selecting New under Active Solution Configuration.
  • Now back on Project Parameters window, add the parameter for the file location to the configurations by pressing the third button from the right above the Name field (this button looks like a rectangle with a wrench next to it). On the window that appears press Add and select the file location parameter. Then edit the values for each configuration, for example to the production configuration would hold "\sharedrive\production."
  • After this, you can toggle between these configurations by selecting the appropriate one from the second drop-down to the left of the green arrow and Start button in SSDT. Whenever the project is deployed to SSISDB, the values from the configuration that's in use in SSDT will be used.

Upvotes: 2

Related Questions