在不同的服务器上使用SSIS环境变量

I have read several articles about Environment variables but I can't find how to apply their usage in my case. I am developing SSIS packages on my local machine. Once they are finished I plan to deploy them on staging an production server. My SSIS project consists of several packages which most of them connect to 2 databases (but each server has it's own copy of db) and few excel files.

我读过几篇关于环境变量的文章，但是我找不到如何在我的案例中应用它们的用法。我正在本地机器上开发SSIS包。完成后，我计划将它们部署到一个生产服务器上。我的SSIS项目包括几个包，其中大部分连接到两个数据库(但是每个服务器都有自己的db副本)和很少的excel文件。

So, I want to deploy my packages to 3 different servers. Based on server, connection strings would be different. Since this is still development phase I would have to redeploy most packages from time to time. What would be the best practice to achieve this?

因此，我想将我的包部署到3个不同的服务器上。基于服务器，连接字符串将是不同的。由于这仍是开发阶段，我将不得不不时地重新部署大多数包。实现这个目标的最佳实践是什么?

1 个解决方案

#1

Creating your folder

In the Integration Services Catalog, under SSISDB, right click and create a folder giving it a name but do not click OK. Instead, click Script, New Query Editor Window. This gives a query like

在SSISDB下的集成服务目录中，右键单击并创建一个赋予其名称的文件夹，但不要单击OK。相反，单击Script，新建查询编辑器窗口。这提供了一个类似的查询

DECLARE @folder_id bigint
EXEC [SSISDB].[catalog].[create_folder]
    @folder_name = N'MyNewFolder'
,   @folder_id = @folder_id OUTPUT
SELECT
    @folder_id
EXEC [SSISDB].[catalog].[set_folder_description]
    @folder_name = N'MyNewFolder'
,   @folder_description = N''

Run that but then Save it so you can create the same folder on Server 2 and Server 3. This will be a theme, by the way

运行它，然后保存它，以便在服务器2和服务器3上创建相同的文件夹。顺便说一下，这将是一个主题

Creating your environment

Refresh the dropdown under the SSISDB and find your newly created folder. Expand it and under Environments, right click and Create New Environment. Give it a name and description but DO NOT CLICK OK. Instead, click Script, New Query Editor Window.

刷新SSISDB下的下拉菜单，找到新创建的文件夹。扩展它和在环境下，右键单击并创建新环境。给它一个名称和描述，但是不要单击OK。相反，单击Script，新建查询编辑器窗口。

We now have this code

我们现在有了这个代码

EXEC [SSISDB].[catalog].[create_environment]
    @environment_name = N'DatabaseConnections'
,   @environment_description = N''
,   @folder_name = N'MyNewFolder'

Run that and save it for deployment to Server 2 and 3.

运行它并将其保存到服务器2和3。

Adding values to an Environment

Refresh the Environments tree and under the Properties window for the newly created Environment, click to the Variables tab and Add your entries for your Connection strings or whatever. This is where you really, really do not want to click OK. Instead, click Script, New Query Editor Window.

为新创建的环境刷新“环境树”并在“属性”窗口下，单击“变量”选项卡并为连接字符串或其他内容添加条目。这就是你不想点击OK的地方。相反，单击Script，新建查询编辑器窗口。

DECLARE @var sql_variant = N'ITooAmAConnectionString'
EXEC [SSISDB].[catalog].[create_environment_variable]
    @variable_name = N'CRMDB'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'DatabaseConnections'
,   @folder_name = N'MyNewFolder'
,   @value = @var
,   @data_type = N'String'
GO
DECLARE @var sql_variant = N'IAmAConnectionString'
EXEC [SSISDB].[catalog].[create_environment_variable]
    @variable_name = N'SalesDB'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'DatabaseConnections'
,   @folder_name = N'MyNewFolder'
,   @value = @var
,   @data_type = N'String'
GO

Run that query and then save it. Now when you go to deploy to environment 2 and 3, you'll simply change the value of @var

运行该查询，然后保存它。现在，当您部署到环境2和3时，您只需更改@var的值

Configuration

To this point, we have simply positioned ourselves for success in having a consistent set of Folder, Environment and Variable(s) for our packages. Now we need to actually use them against a set of packages. This will assume the your packages have been deployed to the folder between the above step and now.

至此，我们已经成功地为我们的包设置了一组一致的文件夹、环境和变量。现在我们需要实际地使用它们来对付一组包。这将假定您的包已经部署到上述步骤之间的文件夹中。

Right click on the package/project to be configured. You most likely want the Project.

右键单击要配置的包/项目。你很可能想要这个项目。

1. Click on the References tab. Add... and use DatabaseConnections, or whatever you've called yours
2. 单击References选项卡。添加……使用DatabaseConnections，或者任何你叫你的
3. Click back to Parameters. Click to Connection Managers tab. Find a Connection Manager and in the Connection String, click the Ellipses and change it to "Use Environment Variable" and find your value
4. 点击返回参数。单击“连接管理器”选项卡。找到一个连接管理器，在连接字符串中，单击省略号并将其更改为“使用环境变量”，然后找到您的值
5. DO NOT CLICK OK! Script -> New Query Editor Window
6. 不要单击OK !脚本->新的查询编辑器窗口

At this point, you'll have a script that adds a reference to environment variable (so you can use it) and then overlays the stored package value with the one from the Environment.

此时，您将拥有一个脚本，该脚本添加对环境变量的引用(以便您可以使用它)，然后将存储的包值与来自环境的包值覆盖。

DECLARE @reference_id bigint
EXEC [SSISDB].[catalog].[create_environment_reference]
    @environment_name = N'DatabaseConnections'
,   @reference_id = @reference_id OUTPUT
,   @project_name = N'HandlingPasswords'
,   @folder_name = N'MyNewFolder'
,   @reference_type = R
SELECT
    @reference_id

GO
EXEC [SSISDB].[catalog].[set_object_parameter_value]
    @object_type = 30
,   @parameter_name = N'CM.tempdb.ConnectionString'
,   @object_name = N'ClassicApproach.dtsx'
,   @folder_name = N'MyNewFolder'
,   @project_name = N'HandlingPasswords'
,   @value_type = R
,   @parameter_value = N'SalesDB'
GO

This script should be saved and used for Server 2 & 3.

这个脚本应该被保存并用于服务器2和3。

Job

All of that makes is so you will have the configurations available to you. When you schedule the package execution from a job, you will end up with a job step like the following

所有这些都是为了让你能得到配置。当您从作业中安排包执行时，您将得到如下所示的作业步骤

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Demo job'
,   @step_name = N'SSIS job step'
,   @subsystem = N'SSIS'
,   @command = N'/ISSERVER "\"\SSISDB\MyNewFolder\HandlingPasswords\ClassicApproach.dtsx\"" /SERVER "\".\dev2014\"" /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

• The Command is obviously the important piece.
• 命令显然是最重要的部分。
• We are running the package ClassicApproach
• 我们正在运行package ClassicApproach
• Run this on the current server with an instance of Dev2014
• 使用Dev2014的实例在当前服务器上运行此操作。
• Use Environment reference 1
• 使用环境参考1
• We use the standard logging level.
• 我们使用标准的日志记录级别。
• This is a Synchronous call meaning that the Agent will wait until the package completes before going to the next step
• 这是一个同步调用，意味着代理将等到包完成后才进入下一步

Environment Reference

You'll notice all of the above was nice and specified text strings instead of random integer values, except for our Environment Reference. That's because you can have the same textual name for an environment in multiple folders. Similar to how you could deploy the same project to multiple folders but for whatever reason, the SSIS devs chose to provide fully qualified paths to a package while we use "random" integer values. To determine your environment ID, you can either run the following query

您将注意到，除了我们的环境引用之外，上面所有的都是漂亮的指定文本字符串，而不是随机整数值。这是因为您可以在多个文件夹中为环境使用相同的文本名称。与如何将相同的项目部署到多个文件夹类似，但是出于某种原因，SSIS devs选择在使用“随机”整数值时为包提供完全限定的路径。要确定您的环境ID，您可以运行以下查询

SELECT
    ER.reference_id AS ReferenceId
,   E.name AS EnvironmentName
,   F.name AS FolderName
,   P.name AS ProjectName
FROM
    SSISDB.catalog.environments AS E
    INNER JOIN
        SSISDB.catalog.folders AS F
        ON F.folder_id = E.folder_id
    INNER JOIN 
        SSISDB.catalog.projects AS P
        ON P.folder_id = F.folder_id
    INNER JOIN
        SSISDB.catalog.environment_references AS ER
        ON ER.project_id = P.project_id
ORDER BY 
    ER.reference_id;

Or explore the Integration Services Catalog under Folder/Environments and double click the desired Environment. In the resulting Environment Properties window, the Name and Identifier will be greyed out and it is the Identifier property value that you need to use in your SQL Agent's job step command for the /ENVREFERENCE value.

或者探索文件夹/环境下的集成服务目录，并双击所需的环境。在“生成的环境属性”窗口中，名称和标识符将显示为灰色，它是您需要在SQL代理的作业步骤命令中为/ENVREFERENCE值使用的标识符属性值。

Wrapup

If you're careful and save every thing the wizard does for you, you have only 1 thing that must be changed when migrate changes throughout your environment. This will lead to clean, smooth, repeatable migration processes and you wondering why you'd ever want to go back to XML files or any other configuration approach.

如果您仔细地保存了向导为您做的所有事情，那么在整个环境中迁移更改时，您只有一件事情必须更改。这将导致干净、平滑、可重复的迁移过程，您可能想知道为什么要返回到XML文件或任何其他配置方法。