如何将SSIS 2012项目配置为在不同的环境配置下运行？ [英] How to configure SSIS 2012 project to run under different environment configurations?

问题描述

使用项目部署模型配置2012 SSIS项目的最佳逻辑方式是什么？

考虑一个SSIS项目的场景 MyImport-Project 有三个包

项目解决方案：

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

项目变量：

• Project-DBConnString：所有包用于连接到单个数据库。


• 项目-FolderPath：所有包使用查找输入文件

环境：

项目部署在

• QA服务器


• 分段服务器


• 生产服务器

有配置项目参数的标准方法吗？

$ b $我看了不少关于软件包的配置文件的文章。有没有办法根据部署环境配置项目参数？

解决方案

SSIS 2012项目部署模式提供更大的灵活性来创建环境并配置环境特定值，可以映射到项目参数。以下是一个示例，说明如何根据多个环境执行部署到 Integration Services Catalog 的软件包。理想情况下，生产环境应该在自己的服务器上。

具有环境特定值的SSIS 2012 Integration Services目录：

假设您已经将一个程序包部署到Integration Services Catalog，其中有两个项目参数名为 环境 和 文件路径 。要在不同环境下运行包，可以在 集成服务目录\ SSIDB \文件夹名称\环境

下创建多个环境>

此示例有三个环境文件夹名为 DEV ， PRD 和 TST

< img src =https://i.stack.imgur.com/kbCon.pngalt =环境文件夹>

创建以后映射的变量到项目参数，双击Environments文件夹下的环境。此示例显示如何在所有环境中配置值。

由于该包具有两个名为 环境 和 FilePath ，我们将创建两个同名的环境变量。所有环境都配置为 相同的 变量名称，但使用 不同 值。

要映射将项目参数的环境变量右键单击集成服务目录下的项目，然后单击 配置...

在配置对话框中，单击 参考 页面，并添加应该与项目关联的每个环境。

在配置对话框中，点击 参数 页面。对于 参数 选项卡下列出的每个参数，您需要单击 省略号 按钮映射相应的环境变量。

在设置参数值对话框中，选择使用环境变量单选按钮并选择适当的变量。这里的参数是 [Practice.SSDT]。[Environment] 。因此，它正在与环境变量 环境 进行映射。

将参数映射到适当的环境变量，您可以在程序包执行期间选择正确的环境。要执行包，请右键单击包，然后单击 执行...

在执行软件包对话框，选中 环境 框，然后选择适当的环境，软件包应从中使用值执行。 >

此示例中的示例包 SO_15206184.dtsx 只需插入项目参数的值 环境 和 FilePath 以下是针对不同环境的程序包执行的结果，即使是包含SSDT内的程序包执行的结果。

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 ?

解决方案

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.

这篇关于如何将SSIS 2012项目配置为在不同的环境配置下运行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！