使用变量处理所有使用 SSIS 的 *.xml 文件 [英] Using a variable to process all *.xml files using SSIS

问题描述

我是 SSIS 包的新手，所以任何帮助都将不胜感激.SSIS 代码是使用 VS2005 构建的，并将其设置为 2005 SQL Server 中的 SQL 代理作业.现在，当我们迁移到 2016 SQL 服务器时，我们看到同一个 SQL 代理作业失败了.因此我们决定使用 VS2012 重新构建包，看看是否能解决问题.

在控制流选项卡上完成的过程:

数据流任务:

使用的变量:

我们使用了 ForEachLoop:

数据流任务中显示的错误:

我们仍然无法确定导致问题的原因，我们还参考了以下链接中给出的完整分步过程.

点击

XML 源高级编辑器:

XML 源组件选项卡:

XML 源连接管理器选项卡:

解决方案

1. 对于面向 SSIS 2016 的软件包，我更愿意使用 VS 2015.
2. 您的错误来自 DataFlow 任务 - XML 源.在包保存时发生的验证步骤，XML 源尝试使用 sXMLfilename 变量值打开 XML 文件；此时sXMLfilename在VS中的值为变量默认值.因此，Visual Studio 中的验证失败，您会看到错误消息.
   要通过验证，XML 源应该能够打开 XML 和 XSD 文件.在开发人员站检查这些文件，必要时从旧服务器迁移文件.对于 XML 文件，您可以使用任何有效的 XML 输入文件，XSD 架构应与其匹配.指定XML 文件的有效路径作为sXMLfilename 变量的默认值.

I am new to SSIS package, so any help would be really appreciated. The SSIS code was built using the VS2005 and its setup as a SQL agent Job in the 2005 SQL Server. Now as we are migrating to 2016 SQL server we see that the same SQL agent Job fails. Hence we decided to rebuild the package using VS2012, to see if that would solve the issue.

Processes done on the Control Flow Tab:

Data Flow task:

Variables used:

we have used a ForEachLoop:

Errors Show in the Data Flow Task:

We are still not able to make out what's causing the issue, we also refereed the full step by step process given in the below link.

Click here!

If any more information is required I can provide that too!!

XML Source Editor Error:

XML Source Advanced Editor:

XML Source Components Tab:

XML Source Connection manager tab:

解决方案

1. I'd rather use VS 2015 for packages targeting SSIS 2016.
2. Your error comes from DataFlow task - XML Source. At Validation step, which occurs on Package Save, XML Source tries to open XML file using sXMLfilename variable value; the value of sXMLfilename at this moment in VS is the variable default value. Therefore, validation in Visual Studio fails and you see the error messages.
   To pass validation, XML source should be able to open both XML and XSD file. Check for these files at Developer's station, migrate files if necessary from your old server. For XML file you can use any valid XML input file, XSD schema should match it. Specify valid path to XML file as the default value for sXMLfilename variable.

这篇关于使用变量处理所有使用 SSIS 的 *.xml 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！