How to configure SSIS 2012 project to run under di

问题:

What would be the best logical way of configuring 2012 SSIS project using the Project Deployment Model?

Consider a scenario of an SSIS Project MyImport-Project having three packages

Project solution:

• MyImport-Project
  • Import-Package-01
  • Import-Package-02
  • Import-Package-03

Project Variables:

• Project-DBConnString : used by all packages to connecting to a single database.
• Project-FolderPath : used by all packages to lookup input files

Environments:

The project gets deployed on

• QA server
• Staging server
• Production server

Is there a standard method of configuring project parameters ?

I saw quite a few articles on Configuration Files for packages. Is there a way to configure Project Parameters depending on the deployment environment ?

回答1:

SSIS 2012 project deployment model provides greater flexibility to create Environments and configure environment specific values, which can mapped to project parameters. Here is a sample that illustrates how you can execute a package deployed to Integration Services Catalog against multiple environments. Ideally, production environment should be on its own server. This example uses all the environments on the same server for simplicity.

SSIS 2012 Integration Services Catalog with environment specific values:

Let's assume that you have deployed a package to Integration Services Catalog with two project parameters named Environment and FilePath. To run the package under different environments, you can create multiple environments under Integration Services Catalogs \ SSIDB \ Folder name \ Environments

This sample has three Environment folders named DEV, PRD and TST

To create variables that should later be mapped to the project parameters, double-click the environment under the Environments folder. This samples shows how to configure values in all environments.

Since, the package has two project parameters named Environment and FilePath, we will create two environment variables of the same name. All the environments are configured with same variable names but with different values.

To map the environment variables to the project parameters, right-click on the project under the Integration Services Catalog and click Configure...

On the Configure dialog, click References page and add each environment which should be associated with the project.

On the Configure dialog, click Parameters page. For each parameter listed under Parameters tab, you need to click the Ellipsis button to map the appropriate environment variable.

On the Set Parameter Value dialog, select Use environment variable radiobutton and choose the appropriate variable. Here the parameter being mapped is [Practice.SSDT].[Environment]. Hence, it is being mapped against the environment variable Environment.

Once you have mapped the parameter against the appropriate environment variable, you can choose the correct environment during package execution. To execute the package, right-click on the package and click Execute...

On the Execute Package dialog, check Environment box and select the appropriate environment from which the package should use the values for execution.

The sample package SO_15206184.dtsx in this example simply inserts the values of project parameters Environment and FilePath into a table. Here are the results of the package execution against different environments, even the one including from within SSDT.

回答2:

From my reading of your question, you have 3 separate servers to deploy to, each with it's own instance of the SSIS Catalog. You do not want a single server to execute for multiple environments. For that scenario, user756519's answer is of little use unfortunately - 10 out of 10 for effort though.

I would ignore the "Environments" setup in SSIS. This is for managing multiple environments on a single SQL Server instance.

Instead I would:

1. set the Environment 1 Project Parameter values in the Visual Studio solution and deploy to Environment 1
2. Deploy to Environment 2
3. Use SSMS to connect to Environment 2, navigate Under Integration Services Catalogs to find your project, right-click it and choose Configure. Edit the parameters as required for Environment 2
4. Deploy to Environment 3
5. Use SSMS to connect to Environment 3, navigate Under Integration Services Catalogs to find your project, right-click it and choose Configure. Edit the parameters as required for Environment 3

You can now continue to develop and deploy your project to each environment. No further maintenance is required, unless you add/delete/rename a Project Parameter. When any package is executed on each Environment/server, it will follow the configuration set via SSMS