RadhaKrishnaKiJaya
asked on
How to trace hanging ssis package
Hello Experts,
I am stuck in a package which was running fine before, but hangs now. I have about 15 tasks to run in the package. Task 1 thru 4 runs just fine. They are just copy the Flat file to local directory, Backup and Delete it etc. The 5th one is insert the data into the database table where it hangs.
FYI, after 1 thru 4, if I try to execute the 5th task independently, it runs without any problem. But fails if I run the whole package.
I added an Event Handler, but it does not show me any errors.
Any idea what else could be going on? Please try to help.
Thank you very much in advance.
I am stuck in a package which was running fine before, but hangs now. I have about 15 tasks to run in the package. Task 1 thru 4 runs just fine. They are just copy the Flat file to local directory, Backup and Delete it etc. The 5th one is insert the data into the database table where it hangs.
FYI, after 1 thru 4, if I try to execute the 5th task independently, it runs without any problem. But fails if I run the whole package.
I added an Event Handler, but it does not show me any errors.
Any idea what else could be going on? Please try to help.
Thank you very much in advance.
Using SQL Profiler to detect what happen when 5th task starts importing data into the table.
ASKER
Raja Jegan R and Dung Dinh,
Thank you for your help. My issue has been resolved already. The reason is, I added breakpoints on the Task. When removed it, it started working.
The question still remains unanswered why it was not moving forward even after hitting F5?
Thank you guys!!!
Thank you for your help. My issue has been resolved already. The reason is, I added breakpoints on the Task. When removed it, it started working.
The question still remains unanswered why it was not moving forward even after hitting F5?
Thank you guys!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you add Break Point, it mean you execute the package in Debug mode. But Component Script on Data Flow, you are not able to debug step by step by pressing F10 or F11 as we can do in Visual Studio .Net
>> The reason is, I added breakpoints on the Task. When removed it, it started working.
Glad that you fixed the issue by yourself..
But I doubt it would be something to do with your breakpoints, you might have some other changes which would have got it to work instead..
Glad that you fixed the issue by yourself..
But I doubt it would be something to do with your breakpoints, you might have some other changes which would have got it to work instead..
>> if I try to execute the 5th task independently, it runs without any problem
With the above symptoms, it would be that the Source or Destination table is blocked by other operations running in parallel.
Kindly check the below things:
1. Where are you fetching the source data from ? if table, then check whether you have NOLOCK placed on your table or QUERY involved.
2. Check whether there are any other operations happening on the destination table like a SELECT query or some other operation.. If so, pls let me know what is it
3. Try running Profiler which can let us know whether there are any other operations happening on both the Source and Destination tables.
4. Check for any blocking in the database when the SSIS package hangs
5. If you are using Fast Load or with Table Lock option, then destination table shouldn't be accessed by anyone for the transfer to start..