August 13, 2009 at 6:18 am
I currently have a number of EXECUTE SQL TASKs setup in my SSIS package. Most of them process correctly, but 90% of the time one particular task hangs.
The task that hangs turns red but there are no error messages (Progress tab) and there is no failure down the failure path... it just hangs.
I can stop the SSIS process, manually execute the stored procedure in the Management Studio (it runs instantly, with no errors), then re-start the SSIS package to complete processing.
I have 4 EXECUTE SQL Tasks that are set in series (one runs, then goes down the success path to the next one) and they are all setup the same. I have not had any failures with the other three.
Connection Type: OLE DB
Connection: [OLE DB to my SQL Server] (SSIS is running on the same server as my SQL Server instance)
SQLSourceType: Direct Input
SQLStatement: EXEC dbo.usp_Upsert
BypassPrepare: True
The stored procedure itself contains 194 lines of code, three temp tables (creations then drops at the end) and a while loop (some complex updates/inserts/deletes depending on the data being processed).
Any troubleshooting ideas would be appreciated! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
October 6, 2009 at 2:02 pm
Ok... took about 40 hours to figure out the problem. Basically, the foreign key relationship between the table I was inserting to and the "parent" table were being violated (due to the developer of the incoming data making a data change without any notifications).
Basically, the stored procedure would hang and not return any error(s), so the SSIS package would not fail.
I put error trapping into the stored procedure which writes out the error(s) to a ErrorLog table. Since the trapping occurs in a while loop, it will capture the error, then continue with the processing. Problem solved! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 15, 2010 at 2:09 pm
Here is a bit more of an update....
First, you can get errors in SSIS that would not fail in SQL Server (in this case, when Ignore_dups is set on)..... here is a high level overview of an example:
http://wiki.sqlis.com/default.aspx/SQLISWiki/IGNORE_DUP_KEYProviderAndDestinationAdapter.html
Second, turning off the ANSI_WARNINGS (SET ANSI_WARNINGS OFF) at the beginning of the stored procedure and then turning it back on again (to ignore messages about aggregates and NULL values, etc.) also kept the SSIS package from just "hanging".
Hope these additional details help others! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply