当前位置：文江博客话题详情

如何在SSIS包中使用间接配置？

发布于 2024-11-25 09:49:18 字数 176 浏览 0 评论 0原文

我创建了一个环境变量
创建了一个包配置，选择了间接 XML 配置并选择了我创建的环境
现在手动创建了与数据库的连接

那么，现在我如何告诉 BIDS 使用步骤 2 中的连接而不是步骤3中的那个？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Hello爱情风 2024-12-02 09:49:18

下面是一个示例，展示了如何实现这一目标。带有屏幕截图的详细过程记录在 此链接。以下是链接中提供的相同内容的纯文本版本。

在打开 BIDS 之前，我们需要创建一个环境变量。单击 Windows 开始按钮，然后单击控制面板。在控制面板上，单击系统和安全。在“系统和安全”窗口中，单击系统。在“系统”窗口的左侧导航栏中，单击高级系统设置。
在“系统属性”对话框中，确保选择“高级”选项卡。在“高级”选项卡上，单击环境变量... 按钮。在“环境变量”对话框中，将有两个环境变量部分，一个基于用户，另一个基于系统。我们将创建新的基于系统的环境变量，以便该变量对计算机而言是通用的，而不是针对每个用户。因此，单击“系统变量”部分下的“新建...”按钮。在“新建系统变量”对话框中，将变量名称设置为 SSIS Indirect Configuration，并将变量值设置为 C:\temp\SSIS_Configurations.dtsConfig。在“环境变量”对话框中，单击“确定”关闭该对话框。单击“确定”关闭“系统属性”对话框。
现在，打开 BIDS 创建一个 SSIS 包。如果您打开了 BIDS，则需要关闭并重新打开 BIDS，以便环境变量更改能够在 BIDS IDE 中正确反映。
在 SSIS 包上，创建两个变量，即 Country 和 State。将变量 Country 设置为值 Canada，将变量 State 设置为 Alberta。
在包上，将脚本任务放置在控制流上，并将其命名为显示变量的值。双击脚本任务以打开脚本任务编辑器。在脚本部分，单击编辑脚本...按钮。脚本任务代码将出现在 VSTA 编辑器中。将 Main() 方法替换为 脚本任务代码 部分下提供的代码。关闭 VSTA 编辑器。单击“确定”关闭脚本任务编辑器。
如果您现在执行该包，您将获得输出Canada – Alberta，因为这些是变量分配的值。
现在，必须创建包配置。可以为包中存在的每个变量创建一个环境变量。在我看来，这很快就会失控。我更喜欢首先创建一个 XML 配置文件并将其保存到本地文件夹。然后，我将更改文件路径以匹配环境变量中使用的路径。以下步骤解释了此过程。
单击该包。选择 SSIS 菜单，然后单击包配置...。在“包配置管理器”对话框中，选中“启用包配置”并单击“添加...”按钮。在“选择配置类型”向导中，选择 XML 配置文件作为配置类型。选择直接指定配置设置单选按钮，并提供路径 C:\temp\Package Configuration.dtsConfig 作为配置文件名。是的，该路径与环境中的路径不匹配，但我们很快就会重命名它。单击“下一步”>按钮。在“选择要导出的属性”上，我只想保存变量的值，因此展开“变量”节点并仅检查变量“国家/地区”和“州”的值属性。有一个单独的节点可用于连接字符串，它允许我们将连接字符串保存到配置文件中。单击“下一步”>按钮。在完成向导步骤时，将配置名称值提供为Package Configuration，然后单击“Finish”。
现在，我们需要创建间接配置。在包配置管理器上，单击添加...按钮。在“选择配置类型”向导中，选择 XML 配置文件作为配置类型。这次选择单选按钮配置位置存储在环境变量中。选择环境变量SSIS 间接配置。这是我们刚刚创建的变量。单击“下一步”>按钮。在完成向导步骤时，提供配置名称值作为间接配置文件，然后单击完成。在“包配置管理器”对话框中，选择配置“间接配置文件”，然后单击右侧的向上箭头将此配置移到“包配置”之前。
当有两个配置具有相同的变量集时，列表中的第二个配置将优先。换句话说，包将仅使用第二个配置中存在的值。复制文件 C:\temp\Package Configuration.dtsConfig 并创建一个名为 C:\temp\SSIS_Configurations.dtsConfig 的新配置文件，以匹配环境变量中提供的路径.
文件 Package Configuration.dtsConfig 包含 Country 的值 Canada 和 State 的值 Alberta。新创建的文件 SSIS_Configurations.dtsConfig 也包含相同的值。我们将 SSIS_Configurations.dtsConfig 中的值更改为 Country 的 United States 和 State 的 California。 p>
如果我们现在运行包，将使用文件 SSIS_Configurations.dtsConfig 中的值，因为间接配置文件位于包配置列表中的第二个。因此，间接配置文件中的值将具有更高的优先级。
即使我们删除名为Package Configuration.dtsConfig的Package Configuration文件，该包仍将继续使用使用配置文件的间接配置文件正确运行>SSIS_Configurations.dtsConfig。此方法有助于仅创建一个可以绑定到 XML 配置文件的环境变量，该配置文件可以包含许多 SSIS 包变量。这避免了为每个包变量创建一个环境变量。我们可以切换包配置文件的位置，而不必以任何方式更改包。我们需要做的就是将环境变量中的值更改为新路径。此更改将自动反映在包中。

希望有帮助。

脚本任务代码：

C#代码，只能在SSIS 2008及更高版本中使用。

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::Country");
    Dts.VariableDispenser.LockForRead("User::State");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(string.Format("{0} - {1}", varCollection["User::Country"].Value, varCollection["User::State"].Value));

    Dts.TaskResult = (int)ScriptResults.Success;
}

Here is an example that shows how this can be achieved. The detailed process with screenshots are documented here at this link. Following is a text-only version of the same available in the link.

Before we open BIDS, we need to create an Environment Variable. Click Windows Start button and click Control Panel. On the Control Panel, click System and Security. On the System and Security window, click System. On the System window’s left side navigation, click Advanced system settings.
On the System Properties dialog, make sure Advanced tab is selected. On the Advanced tab, click Environment Variables… button. On the Environment Variables dialog, there will be two sections of Environment Variables, one user based and the other system based. We will create new System based environment variable so that the variable is common to the machine and not per user basis So, click on the New… button under the System variables section. On the New System Variable dialog, set the Variable name to SSIS Indirect Configuration and set the Variable value to C:\temp\SSIS_Configurations.dtsConfig. On the Environment Variables dialog, click OK to close the dialog. Click OK to close the System Properties dialog.
Now, open BIDS create an SSIS package. If you had BIDS open, you need to close and re-open the BIDS so that the Environment Variable changes are correctly reflected within BIDS IDE.
On the SSIS package, create two variables namely Country and State. Set the variable Country with value Canada and the variable State to Alberta.
On the packages, place a Script Task on the Control Flow and name it as Display variable’s value. Double-click on the Script task to open the Scrip Task Editor. On the Script section, click Edit Script… button. The script task code will appear in VSTA editor. Replace the Main() method with the code provided under Script Task Code section. Close the VSTA editor. Click OK to close the Script Task Editor.
If you execute the package now, you will get the output Canada – Alberta because those are the values the variables are assigned with.
Now, the package configurations have to be created. It is possible to create one Environment variable for each variable present in the package. In my opinion, that will quickly get out of hand. I prefer to first create an XML configuration file and save it to a local folder. Then, I will change the file path to match the path used in the Environment variable. The following steps explain this process.
Click on the package. Select SSIS menu and then then click Package Configurations…. On the Package Configurations Organizer dialog, check Enable package configurations and click Add… button. On the Select Configuration Type wizard, select XML configuration file as the Configuration type. Select Specify configuration settings directly radio button and provide the path C:\temp\Package Configuration.dtsConfig as the Configuration file name. Yes, this path doesn’t match the path in the environment but we will rename it shortly. Click Next > button. On Select Properties to Export, I would like to save only the values of the variables, so expand the Variables node and check only the Value Properties of the variables Country and State. There is a separate node available for connection string, which will allow us to save the connection strings to the configuration file. Click Next > button. On Completing the Wizard step, provide the Configuration name value as Package Configuration and click Finish.
Now, we need to create the indirect configuration. On the Package Configuration Organizer, click Add… button. On the Select Configuration Type wizard, select XML configuration file as the Configuration type. This time select the radio button Configuration location is stored in an environment variable. Select the Environment variable SSIS Indirect Configuration. This is the variable that we just created. Click Next > button. On Completing the Wizard step, provide the Configuration name value as Indirect Configuration File and click Finish. On the Package Configurations Organizer dialog, select the configuration Indirect Configuration File and click the Up arrow on the right-hand side to move this configuration ahead of the Package Configuration.
When there are two configurations with the same set of variables, the second configuration in the list will take precedence. In other words, package will use only the values present in the second configuration. Copy the file C:\temp\Package Configuration.dtsConfig and create a new configuration file named C:\temp\SSIS_Configurations.dtsConfig to match the path provided in the Environment Variable.
The file Package Configuration.dtsConfig contains the values Canada for Country and Alberta for State. The newly created file SSIS_Configurations.dtsConfig also contains the same values. We will change the value in the SSIS_Configurations.dtsConfig as United States for Country and California for State.
If we run the package now, the values from the file SSIS_Configurations.dtsConfig will be used because the Indirect Configuration File is second on the list of Package Configurations. So, the values in the Indirect Configuration File will have higher precedence.
Even if we delete the Package Configuration file named Package Configuration.dtsConfig, the package will continue to run correctly using the Indirect configuration file that uses the configuration file SSIS_Configurations.dtsConfig. This method helps to create only one Environment Variable that can tie back to an XML configuration file which can contain many SSIS package variables. This avoids creating one Environment variable for each package variable. We can switch the location of the package configuration file without having to change the package in anyway. All we need to do is change the value in the Environment Variable to the new path. This change will automatically reflect in the package.

Hope that helps.

Script Task Code:

C# code that can be used only in SSIS 2008 and above.

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::Country");
    Dts.VariableDispenser.LockForRead("User::State");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(string.Format("{0} - {1}", varCollection["User::Country"].Value, varCollection["User::State"].Value));

    Dts.TaskResult = (int)ScriptResults.Success;
}

回复收藏 0 原文