SSIS package troubleshooting

[WannaFly]

We moved to SQL2005, I developed all the SSIS packages and everything works relatively well except one package. I have about 8-10 packages that are scheduled to run every night. (seemingly) Randomly, one of the packages fails once a week or once every ten days or so. All I get in the log is:

Message
The job failed. The Job was invoked by Schedule 7 (Holds - Sat Night). The last step to run was step 1 (run SSIS).
and on the step:
Message
Executed as user: SQL\SYSTEM. The package execution failed. The step failed.


There is nothing in the server logs, the event log, anywhere that I can find as to WHY this package fails.

How can I get more detailed errors without having to re-deploy the package? I've read about logging but it seems like you have to add it in BIDS and then re-add the package to the server, which seems odd to me. There has to be a way to get more detailed information as to why it is failing.

TIA for any help, I'm pretty new to SSIS.

 

[imported_Dhaval00]

Is this a SQL Server Agent job? If so, connect to your instance of SQL Server, go to SQL Server Agent > Jobs > [Your Job]. Right click on the job and select 'View History.' The day the job failed should have a red crossmark against it. Expand the job node for that day and select the step on which the job failed (if you don't select the proper step, you won't be able to see the exception). At the bottom of the same dialog box, if you scroll around, you should see the detailed exception as to why the step/execution failed.

As a side note, why aren't you logging internally? Was just an oversight?

Also, what kind of tasks are you performing inside the package?

 

[WannaFly]

Dhaval00,
Yes - it's running as as SQL server agent job. Where you told me to go is what i pasted as the messages above, there is NO detailed information, just "Message
Executed as user: SQL\SYSTEM. The package execution failed. The step failed."

I'm not logging internally, I didn't know much about SSIS, they are migrated DTS packages (re-written completely) and in DTS if it failed it gave a good error, so I didn't know I had to do internal logging. I am trying to avoid re-deploying these packages since most of the time, they work fine. I'm still trying to grasp the SSIS deployment, it's so different then DTS, which I was very used to.

Kinds of tasks: writing to a file, uploading file to FTP

It only runs once a day, and no other process touches that file - and the FTP server isn't rejecting anything, i've checked its logs. I'm at a loss here.

 

[KLin]

See which step actually failed in the job history. There should be a more descriptive error in the details of the step execution.

 

[imported_Dhaval00]

Do you have the luxury of running this package by itself in production? The message you posted in your second post seems to indicate that SSIS is unable to grasp on to an exception [which is weird because I have never seen it done that during my development]. The reason why I ask this is because there is a way where in you can execute the package (separate copy of the dtsx file) via the DTS UI [now the SSIS UI] from within SQL Server Management Studio. Doing so will allow you to visually pinpoint as to where exactly the package is failing (and why it is failing). This UI is clone of the "Package Progress" tab in BIDS when you're debugging a package.

 

[WannaFly]

KLin: It doesn't tell me which step.

Dhaval: I can run in in production, but it doesnt help because it works, and it's been working the past few nights. It really is random as to when it doesn't work. Whenever i run it through BIDS, manually, or even as an agent, it always succeeds.

 

[KLin]

Maybe a permissions issue when writing the file to the file system?

 

[KLin]

http://msdn.microsoft.com/en-us/library/ms138039.aspx

I would read that article to help with troubleshooting.

 

[imported_Dhaval00]

Well... what I was going to propose following my previous post was to actually modify your package and add logging on the tasks. Then run this package from within Management Studio or BIDS. Another thing you can try is take the FTP server offline and see if you get the same error... essentially, if your package fails without logging an error, you would know it's an FTP issue.

I have run into similar scenarios in some of my packages, but all of them have had to do with the server being pegged too much or tempdb seeing heavy loads (This is logged as a PrimeOutputBuffer error). In your case, you aren't even seeing an exception, which could have to do with a failing FTP request?

Another option is to email me the package or something if it's as simple as it sounds. I could look at it if I find the time.

 

[WannaFly]

KLlin: I'll take a look through that..it looks as if I'll be putting logging in all my packages from now on.

Dhaval: I'm relatively sure it's not the FTP server. There are abou 5 other jobs that utilize it that all run before and after it, and they all run perfect. I've checked the FTP logs and there is no connection denied/etc in it. I've confirmed it can have multiple logged in (in case the jobs overlap), and none of the jobs "share" files.

I might email you the package - but not sure how you could even look at it, since you wont have my database structure, cant access the FTP, etc.

Thanks for all the assistance..i hope to get this figured out.

 

[WannaFly]

Well, the job failed again last night -after 3 nights of working fine. I'm going to bite the bullet and try to put logging in it today.

 

[WannaFly]

I enabled SSIS loggin and got this error (finally!):

failed with the following error: "Transaction (Process ID 54) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, it appears to be a deadlock, I've never had to deal with one before, so i'm off to figure out how to troubleshoot it.