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

我已经阅读了几篇有关环境变量的文章，但找不到如何在我的情况下应用它们的用法。我正在本地计算机上开发SSIS软件包。一旦完成，我计划将它们部署在生产服务器上。我的SSIS项目由几个程序包组成，其中大多数程序包都连接到2个数据库(但是每个服务器都有它自己的db副本)和几个excel文件。

因此，我想将软件包部署到3台不同的服务器上。根据服务器，连接字符串将有所不同。由于这仍然是开发阶段，因此我将不得不不时重新部署大多数软件包。实现此目标的最佳做法是什么？

最佳答案

建立资料夹

在SSISDB下的Integration Services Catalog中，右键单击并创建一个为其命名的文件夹，但要使用而不是，请单击“确定”。而是单击“脚本”，“新建查询编辑器”窗口。这给出了类似的查询

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''

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

创建您的环境

刷新SSISDB下的下拉菜单，然后找到您新创建的文件夹。展开它，然后在“环境”下，右键单击并创建新环境。给它一个名称和描述，但不要单击确定。而是，单击“脚本”，“新建查询编辑器”窗口。

我们现在有了这段代码

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

运行该文件并将其保存以部署到服务器2和3。

向环境添加值(value)

刷新“环境”树，并在新创建的环境的“属性”窗口下，单击“变量”选项卡，然后为“连接”字符串或其他内容添加条目。您确实是在这里，确实不想单击“确定”。而是单击“脚本”，“新建查询编辑器”窗口。

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

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

配置

到目前为止，我们只是简单地将自己定位为成功，为我们的软件包提供了一致的文件夹，环境和变量集。现在，我们需要针对一组程序包实际使用它们。这将假定您的软件包已在上述步骤到现在之间部署到了该文件夹中。

右键单击要配置的程序包/项目。您最可能想要该项目。

单击“引用”选项卡。添加...并使用DatabaseConnections或您所谓的

单击返回到“参数”。单击“连接管理器”选项卡。找到一个连接管理器，然后在连接字符串中，单击省略号并将其更改为“使用环境变量”，然后找到您的值

不要点击确定!脚本->新建查询编辑器窗口

此时，您将拥有一个脚本，该脚本添加了对环境变量的引用(以便可以使用它)，然后将存储的程序包值与“环境”中的值重叠。

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

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

工作

所有这些都是为了让您拥有可用的配置。当您计划某个作业的程序包执行时，您将得到一个类似于以下内容的作业步骤

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'

命令显然是重要的部分。

我们正在运行软件包ClassicApproach

使用Dev2014实例在当前服务器上运行

使用环境引用1

我们使用标准的日志记录级别。

这是一个同步调用，这意味着代理将等待包完成，然后再进行下一步

环境引用

您会注意到，上述所有内容都很不错，并且指定了文本字符串，而不是随机整数值，但我们的《环境引用》除外。这是因为您可以在多个文件夹中为环境使用相同的文本名称。与您可以将同一项目部署到多个文件夹的方式类似，但是无论出于何种原因，SSIS开发人员都选择在使用“随机”整数值的同时为软件包提供完全限定的路径。要确定您的环境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;

或浏览“文件夹/环境”下的Integration Services目录，然后双击所需的环境。在出现的“环境属性”窗口中，“名称”和“标识符”将变为灰色，这是您需要在SQL Agent的job step命令中使用Identifier值的/ENVREFERENCE属性值。

包起来

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

关于sql-server - 在不同的服务器上使用SSIS环境变量，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28684345/