sql-server - Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37

Question

I know other people have already posted similar error and got multiple answers/solutions, None of these worked for me. also I AM A NEWBIE, so here is my situation:

So I created a package that has a foreachloop and within it a data task flow. I have a source with 20 excel files. The whole thing runs perfectly fine. I finished the project deployment and set up a job agent on my sql server. and to make sure job agent run right, I truncated my tables on the database. when I manually started the job, it did not populate the tables as supposed to. I went back into SSIS packages and found these error messages:

enter image description here

[Excel Source ProspectFiles [182]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

[Excel Source ProspectFiles [182]] Error: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

[SSIS.Pipeline] Error: "Excel Source ProspectFiles" failed validation and returned validation status "VS_ISBROKEN".

well, it appears that instead of the foreachloop running all the excel files, it only runs the 1st file.

enter image description here

标签： sql-serverexcelssisforeach-loop-container

Answer 1

您的 Excel 连接管理器必须使用变量来引用循环正在更新的文件的名称。检查该变量的默认值是否仍然有效。

此外，如果您在您的机器上开发了包，然后将其部署到另一台服务器，那么该包不再在您的机器上查找文件，而是在您将包部署到的服务器上查找它。