August 11, 2022 at 2:48 pm
I have three SSIS packages created in Visual Studio 2019, going against a SQL Server 2016 database that imports 6 Excel files (I know Excel is not a recommended filetype for this, but unfortunately for various reasons, we have to use Excel). Each package imports to a different table (the data is all related, which is why this is being done as a group), so I created three packages, one for each table. I never have a problem with any of these packages when I run it through Visual Studio to test / verify they work. If I double click on the *.dtsx file outside of Visual Studio and execute it through the "Execute Package Utility", I never had a problem. However, when I attempt to run this package in a SQL Job (with Run As set as SQL Server Agent Service Account), two of the three packages start to have issues. I say "most" of the time, two of the package fail but the one NEVER fails. It is my belief this is due to simply the number of records in each Excel file.
Table / Package 1: 220,000 records
Table / Package 2: 180,000 records
Table / Package 3: 8,000 records
As you can see, Package 1 and Package 2 attempt to import about 200,000 records in all 6 Excel files, but Package 3 only has 8,000 records in all 6 Excel files. Due to this, this is my belief the issue relates to the number of records. The best error message I get is "Execution Terminated" for Packages 1 / 2. I get the same issue whether the package source is SSIS Catalog or File System.
I attempted to increase the desktop heap size on the server based on the following article (https://techcommunity.microsoft.com/t5/sql-server-support-blog/ssis-package-fails-with-error-the-step-did-not-generate-any/ba-p/318301) but this didn't help either.
I thought perhaps the time of day I ran these might be an issue due to server load, but I ran them very early in the morning when no one else was on the server and still got the error.
I am sort of out of ideas and hoping someone has some thoughts on what to try or might even know how to fix this.
August 12, 2022 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply