SSIS package keeps hanging with no errors at same point
-
Hi All,
I'm hoping I can get some feedback on an issue I've been having with a package I've modified. It runs fine locallywithin Visual Studio BIDS, but when I try to run it in my sandbox, DEV, or INT , itkeeps hanging at the same exact spot (at 21% complete), but with no errors atall. I’ve tried recreating the table that it loads before running thispackage, but it didn’t help.
This is the modification I’ve done to the package:
Has anyone ever experienced something similar and do you know what could be causing this? Thanks in advance for your insight!
-
I had this happen to me a while ago. The circumstances were a bit specific, so it’s likely not causing your problem, but just in case.
In my case the issue was a query in a stored procedure. The SP used a dozen or so CTEs, and ran fine in Visual Studio on my Dev PC against my local Dev database. In the past, it also ran fine against our Prod database, also from with Visual Studio on my Dev PC. It’s an on demand thing that I hadn’t run for six months or so. This time when I ran it, it hung on the dataflow that started with that SP. I, the past, it took two minutes tops. Now I was killing it after 30 mins, though there was no deadlock, and SP_WHO2 showed ongoing I/O.
The SP used the results from one of its first CTEs in most of the remaining CTEs. Changing that CTE to a table variable solved the issue, and it was back to running in a minute or two. A little rearranging of the query was needed; another CTE had to be changed to a table variable as well, only because it was no longer available after the insert to the first table variable.
I’d be interested in hearing from anyone who can explain why the change to table variables fixed the problem.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply