SSIS Package Execution Fails when large size Excel File is loaded via SSIS Catalog (SSISDB)

▲
0
▼

the package execute successfully from VS but when deploy on SSIS catalog it gives following error Error: The Execute method on the task returned error code 0x80070008 (Could not load file or assembly 'System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)). The Execute method must succeed, and indicate the result using an "out" parameter

Excel File size is 147 mb

excel
ssis
etl
asked on Stack Overflow Sep 11, 2018 by Fahad dogar

2 Answers

▲
0
▼

Yes, This problem occurs when you try to load excel file through SQL server till 2012. The limit is 120MB split your file to less than this size and try to load again. Second solution is try SQL server 2016 and you will not face this issue.

answered on Stack Overflow Sep 11, 2018 by Shweta Tiwari
▲
0
▼

Microsoft has acknowledged this shortcoming and has released the following package to rectify it using "newer" Access/Excel Connection managers. However, after installing and following the instructions - the same issue occurs.

The cause of the problem: The problem is caused due to a limitation that exists with office versions 1997-2003. The connection manager was designed for Office 1997 and has not been amended or improved since. This is why, when you actually research limitations on Office 2000 or 2003, you'll see that they do not allow more than 255 columns (the limit mentioned above) AND the length of the column-name cannot surpass 64 characters. These are software limitations that existed with Office 2000 and 1997 and since the Connection Manager was designed then, they just remained with it given MS did not invest in improving or updating their product. Hope this helps!

In my other consideration it may due to OLEDB provider version older version issue Follow below link it may help you a lot

[https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/][1]

Last Solution:

As you said your package is working fine but not in deployment mode right ? So why didn't you try DTEXEC command line execution for this package. Schedule it using Windows Task Scheduler Follow below link.

https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/

answered on Stack Overflow Sep 11, 2018 by Adeel Nazir • edited Sep 18, 2018 by Adeel Nazir

User contributions licensed under CC BY-SA 3.0