是否可以避免 XML 配置文件并仍然在 SSIS 中使用包配置? [英] Is it possible to avoid XML config files and still use package configuration in SSIS?

问题描述

我们的团队管理多个版本的 dtsconfig 文件，每个发布环境一个，我想看看有没有办法避免这种情况，看看有没有更简单的方法.

我们的问题是指定在哪里可以找到输入文件.在每个发布环境中，输入文件位于与托管包文件的服务器不同的服务器上，我怀疑这是常态.

理想情况下，我希望能够使用环境变量指定文件 ConnectionString 来指定文件夹位置，但保持文件名部分不变，如下例所示:

%FileFolder%\MyFile.txt.

..其中 %FileFolder% 等于 \OurServerName\OurProjectName\OurFilesFolder.

<DTS配置><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="MyDomain\MyIDE" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="05/30/2009 01:26:00 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[FLAT_FILE_PLAN].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>\\OurServerName\OurProjectName\OurFilesFolder\MyFile.txt</ConfiguredValue></配置></DTSConfiguration>

解决方案

这里有一个可能的解决方案，您可以尝试在环境变量中指定文件夹路径并在您的包中使用它.此示例不使用配置文件 (.dtsconfig)，但它需要在包所在的机器上设置一个名为 SSISFolderPath 的 Environment Variable被处决.

分步过程:

1. 导航到控制面板 --> System --> 高级系统设置 --> 点击Advanced 选项卡 --> 单击 Environment Variables... 按钮 --> 单击对话框底部的第二个 New... 按钮.这些步骤适用于 Windows Server 2008 或 2008 R2.较旧的 Windows 服务器的导航选项可能略有不同.

2. 创建一个环境变量，如屏幕截图 #1 所示.我将它命名为 SSISFolderPath 并赋予值 c:\temp\SSISFolderPath\

3. 注意:如果您打开了 Visual Studio IDE，请关闭并重新打开它，以便 IDE 可以看到环境变量设置为以及在 SSIS 包中.

4. 在 SSIS 包上，创建一个名为 FilePath 的变量，如屏幕截图 #2 所示.了解如何在 SSIS 包中创建变量.

   屏幕截图 #3:

   屏幕截图 #4:

   屏幕截图 #5:

   屏幕截图 #6:

   屏幕截图 #7:

   屏幕截图 #8:(此屏幕截图仅适用于 SSIS 2005)

   Our team manages multiple versions of dtsconfig files, one for each release environment, and I am trying to see if there is a way to avoid this and see if there is a simpler way.

   Our issue is with specify where to find the input file. In each release environment, the input files is on a server that is different than the server which hosts the packages file, which I suspect is the norm.

   Ideally, I would like to be able to specify a file ConnectionString using an Environmental variable to specify the folder location but leave the file name portion constant as in the following example:

   %FileFolder%\MyFile.txt.

   ..where %FileFolder% is equal to \OurServerName\OurProjectName\OurFilesFolder.

   <?xml version="1.0"?>
   <DTSConfiguration>
       <DTSConfigurationHeading>
           <DTSConfigurationFileInfo GeneratedBy="MyDomain\MyIDE" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="05/30/2009 01:26:00 PM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[FLAT_FILE_PLAN].Properties[ConnectionString]" ValueType="String">
           <ConfiguredValue>\\OurServerName\OurProjectName\OurFilesFolder\MyFile.txt</ConfiguredValue>
       </Configuration>
   </DTSConfiguration>
   

   解决方案

   Here is a possible solution that you can try to specify the folder path in an environment variable and use that in your package. This example doesn't use configuration file (.dtsconfig) but it requires an Environment Variable named SSISFolderPath set up on the machine where the package is being executed.

   Step-by-step process:

   1. Navigate to Control Panel --> System --> Advanced system settings --> click on the Advanced tab --> Click on the Environment Variables... button --> Click on the second New... button at the bottom of the dialog. These steps are valid for Windows Server 2008 or 2008 R2. Older Windows Servers might have slightly different navigation options.

   2. Create an environment variable as shown in screenshot #1. I have named it as SSISFolderPath and gave the value c:\temp\SSISFolderPath\

   3. NOTE: If you have the Visual Studio IDE open, please close and re-open it so that the Environment variable settings are visible to the IDE as well as in the SSIS package.

   4. On the SSIS package, create a variable named FilePath as shown in screenshot #2. Refer this link to know how to create a variable in SSIS package.

   5. On the SSIS package, click on the menu SSIS and select Package Configurations... Please make sure that you click on the package before you do this. Otherwise, the option will not be visible.

   6. Select Enable package condifurations checkbox and click Add... button. Refer screenshot #3.

   7. On the Select Configuration Type step, select the value Environment variable from the drop down Configuration type and select the newly created variable, here in this example it is SSISFolderPath, from the dropdown Environment variable. Click Next > button. Refer screenshot #4.

   8. On the Select Target Property step, expand the Variables section and expand the Properties of the variable FilePath and select Value under Properties node. Click Next > button. Refer screenshot #5.

   9. On Completing the Wizard step, provide a suitable Configuration name. I have given Environment_Variable. Click Finish button. Refer screenshot #6.

   10. I have placed a Script task on the Control Flow tab of the SSIS package just to demonstrate that the variable is populated with value from Environment Variables. Note that the variable currently has no value on the package. In the following step when the Script task is invoked during package execution, the variable will be populated with the value from Environment Variables SSIFolderPath created on the machine and will display it in a MessageBox. Refer screenshot #7 for sample output.

   Hope that helps.

   Script Task code: (Use the code given below to replace the Main() method in your Script task)

   VB Main() method code that can be used in SSIS 2005 and above

   Public Sub Main()
       Dim varCollection As Variables = Nothing    
   
       Dts.VariableDispenser.LockForRead("User::FilePath")
       Dts.VariableDispenser.GetVariables(varCollection)
   
       MessageBox.Show(varCollection("User::FilePath").Value.ToString())
   
       Dts.TaskResult = ScriptResults.Success
   End Sub
   

   C# Main() method code that can be used only in SSIS 2008 and above .

   public void Main()
   {
       Variables varCollection = null;
       Dts.VariableDispenser.LockForRead("User::FilePath");
       Dts.VariableDispenser.GetVariables(ref varCollection);
   
       MessageBox.Show(varCollection["User::FilePath"].Value.ToString());
       Dts.TaskResult = (int)ScriptResults.Success;
   }
   

   Screenshot #1:

   Screenshot #2: Refer this link to know how to create a variable in SSIS package.

   Screenshot #3:

   Screenshot #4:

   Screenshot #5:

   Screenshot #6:

   Screenshot #7:

   Screenshot #8: (This screenshot is applicable only for SSIS 2005)

   这篇关于是否可以避免 XML 配置文件并仍然在 SSIS 中使用包配置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！