如何使用 ssis(从 Web 门户下载的文件)将 XML 电子表格 2003(.xml) 加载到 SQL 服务器中 [英] How to load an XML Spreadsheet 2003(.xml) in to SQL server using ssis (file downloaded from web portal)

问题描述

遇到一个问题.需要从门户网站下载文件.现在我设法使用 SSIS 脚本任务来做到这一点.真正的问题来了.

现在，当我将文件下载为 .xls 时.下载的文件似乎是 Excel 97-2003 工作表，但实际上不是.这是一个 XML 电子表格 2003(.xml).

现在，当我尝试打开这个下载的文件时，它出现了一条错误消息(已附加).因此，我的 SSIS 包无法将文件作为 Excel 源打开，我什至无法使用 XML 源编辑器，因为 xml 编辑器不支持文件中的某些字段值，因此它会引发错误消息.

当我打开下载的文件并将其保存为真正的 .xls、xlsx 或 csv 文件时.然后它工作得很好并且包加载了文件.

当我尝试从 Portal 下载 .csv 或 .txt 文件时，它以奇怪的格式下载文件.

所以我现在被困住了，我尝试了很多不同的方法，但它不起作用.我还尝试了 ssis 中的文件任务系统"功能来重命名下载的文件.即使文件扩展名已更改，但源代码编辑器仍然不喜欢文件格式.任何帮助将不胜感激.

解决方案

我遇到了同样的问题.我使用了以下 Excel 连接管理器连接字符串，它对我有用:

<预><代码>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\MyXMLFile.xls;Extended Properties="EXCEL 12.0 XML;HDR=YES;";

Struggling with an issue. Need to download a file from a webportal. Now i managed to do that using SSIS script task. Here comes the real issue.

Now when i download the file as .xls. Downloaded file appears to be Excel 97-2003 Worksheet but actually it isn't. It's an XML Spreadsheet 2003(.xml).

Now when i try to open this downloaded file, it comes up with an error message (Attached). So my SSIS package can't open the File as an Excel source and i can't even use XML source editor as some of the field values in file are not supported by xml editor so it throws an error message.

When i open the downloaded File and save it as a genuine .xls, xlsx or csv file. Then it works perfectly fine and package loads the file.

when i try to download it from Portal as a .csv or .txt, it downloads the file in strange format.

So i am stuck now, I have tried so many different approaches, but its not working. I have also tried "File Task System" functionality in ssis to rename the downloaded file. Even though file extension is changed but source editor still won't like the file format. Any help with would be appreciated.

解决方案

I had this same issue. I used the following Excel Connection Manager connection string and it worked for me:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\MyXMLFile.xls;Extended Properties="EXCEL 12.0 XML;HDR=YES;";

这篇关于如何使用 ssis(从 Web 门户下载的文件)将 XML 电子表格 2003(.xml) 加载到 SQL 服务器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！