无法使用ssis包将excel导入sql服务器 [英] failed to import excel to sql server using ssis package

问题描述

您好，我正在开发一个sis软件包，该软件包将excel文件(.xlsx)从ftp服务器导入到本地文件夹，然后将它们导入到sql server表中.我正在使用 foreach 映射到文件名.从ftp服务器到本地的导入工作正常，但是从本地文件夹到sql表的导入失败.

Hi im developping an ssis package that imports excel files (.xlsx) from an ftp server to a local folder then they are imported to a sql server table . I'm using a foreach mapping to the name of files. The import from the ftp server to local work fine, but the import from the local folder to the sql table failed.

似乎我在excel源中有问题.这些是错误:

It seems that I have a problem in excel source. These are the errors:

Start SSIS package "Package.dtsx."
Information: 0x1 at Script Task, C # My Message: System.Collections.ArrayList
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase begins.
Error: 0xC0202009 at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 to Flow Task data, Excel Source [1]: Failed to open a rowset for "Sheet1 $". Verify that the object exists in the database.
Error: 0xC004706B to Flow Task data SSIS.Pipeline: validation failed "component" Excel Source "(1)". Returned validation status "VS_ISBROKEN."
Error: 0xC004700C to Flow Task data SSIS.Pipeline: Failed to validate one or more components.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors (6) reached the maximum allowed (1); leading to a failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the value of MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Success.
The program '[5504] Package.dtsx: DTS' has exited with code 0 (0x0).

我的配置如下:

对于Excel Manager连接，我为connectionString = @ [User :: variable1] + @ [User :: DOWNLOAD_DIRECTORY_LOCAL] + @ [User :: FTP_FILE_URL] + @ [User :: variable2]作了一个表达式

For the excel manager connexion, I made an expression for connectionString = @[User::variable1] + @[User::DOWNLOAD_DIRECTORY_LOCAL] + @[User::FTP_FILE_URL] + @[User::variable2]

变量1 = Provider = Microsoft.ACE.OLEDB.12.0;数据源=

变量2 =;扩展属性="EXCEL 12.0; HDR = YES";

我还将数据流任务，ftp任务，foreach任务和excel连接的延迟验证属性设置为true.

I made also the delay validation property to true for data flow task, ftp task, foreach task and excel connection.

推荐答案

我刚刚写了一个程序包，自己做同样的事情.按此顺序检查的事情:

I just wrote a package to do the very same thing myself. Things to check in this order:

您是否已在Excel数据连接中的

1. 浏览到本地文件夹中的excel文件(一旦它们存在)并选择了一个(在开发时需要在其中复制一个)?因此，当您转到数据流任务(位于For Each内部)中的excel源对象时，可以选择Excel数据连接，然后在"excel工作表名称"下看到Sheet $ 1?

1. in your Excel Data Connection have you browsed to the excel files in your local folder (once they are there) and selected one (you need to copy one in there while developing)? so when you go to your excel source object inside your Data Flow Task (inside the For Each) you can select the Excel Data Connection and then see Sheet$1 under "name of the excel sheet"?

确定您已经完成上述操作之后，再在Excel数据连接上单击鼠标右键，然后在Expressions属性中添加ExcelFilePath = @ [User :: FTP_FILE_URL]?(请注意，您需要在对于每个容器"的收集"选项卡上的检索文件名"下选择完全合格")

Once you are sure you have done above have you then right-clicked on the Excel Data Connection and in the Expressions property added ExcelFilePath = @[User::FTP_FILE_URL]? (note you need to select 'Fully Qualified' under Retrieve File Name on the Collection tab of the For Each container)

中，您是否为.xlsx文件选择了正确的版本(Excel 2007)或为.xls选择了Excel 2003?我注意到一个小错误，当我将文件名更改为默认值回到2007年时，不得不(再次)手动将其更改为2003年.

in your Excel Data Connection have you selected the right version (Excel 2007) for the .xlsx files or Excel 2003 for .xls? I noticed a small bug where when I changed the filename it defaulted back to 2007, I had to manually change it back (again) to 2003.

在运行步骤之前，检查文件夹中至少存在一个工作簿.此处有一些有关如何添加脚本任务以验证User :: DOWNLOAD_DIRECTORY_LOCAL中至少一个文件的代码.

Check at least one workbook exists in the folder before the step runs. There is some code around here about how to add a script task to validate at least one file being in User::DOWNLOAD_DIRECTORY_LOCAL.

我遇到了大量有关Microsoft.ACE.OLEDB.12.0驱动程序的错误，再加上64位服务器出现问题，不得不将软件包包装在工作中并检查使用32位"作业属性中执行选项下的运行时"选项.检查驱动程序是否正常运行(尽管如果设置不正确，通常会给出特定的驱动程序错误).

I got a load of errors about the driver for Microsoft.ACE.OLEDB.12.0, plus had issues with a 64-bit server and had to wrap the package in a job and check the 'use 32-bit runtime' option under execution options in the job properties. Check the driver is working OK (although it usually gives a specific driver error if you haven't got it set up right).

嗯，那是在我回家之前很快得到的.让我知道它是否有效还是失败.

Um that's it offhand just quickly before I head home. Let me know if it works or is still a fail..

这篇关于无法使用ssis包将excel导入sql服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！