?
Solved

SSIS - How to get the detailed error when a control flow task fails

Posted on 2010-03-30
4
Medium Priority
?
653 Views
Last Modified: 2013-11-26
Hi,

I would like to log errors when my tasks fails in the control flow. I'm using the logging system and errors are logged fine. My problem is theses errors are not enough detailed.

I have a FTP task that move sends files. When my task fails, the error logged is  "Connection manager "FTP Connection Manager" ". But when I check the Progress pane i have a more detailed error description:

Error: An error occurred in the requested FTP operation. Detailed error description: Sending file "...\Success\zmmim51_20100326_085357.cmp".
File "/.../DTS/Upload/zmmim51_20100326_085357.cmp" already exists.

This is what i want to be logged in my file.

I tried to append log in a script task, but I only have access to ErrorCode and ErrorDescription. I looked the ComponentMetaData object but it seems to be only usable in Data Flow tasks. I may be wrong.


Thanks for helping!
0
Comment
Question by:TelDig
  • 2
4 Comments
 
LVL 6

Expert Comment

by:yogi4life
ID: 29110798
Are you using SSIS in SQL Server 2005 or 2008?
0
 
LVL 5

Accepted Solution

by:
grzegorzs earned 2000 total points
ID: 29190476
Add your code (for example Execute SQL Task or Script Task) to OnProgress and/or OnError Event.

Detailed instructions here:
http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

best regards
Grzegorz
0
 

Author Comment

by:TelDig
ID: 29206612
I'm using SSIS in SQL Server 2008

I already tried this, but in the script task I only have access to ErrorCode and ErrorDescription variables. And again, when my FTP task fail or any other control flow task, I only get non-descriptive error like 'Unable to send files using "FTP Connection Manager"'.

But I know more detailed error exists because the Progress/Results pane show me what happened.
0
 
LVL 5

Expert Comment

by:grzegorzs
ID: 29223417
You can log these messages by using standard logging in SSIS package. Enable logging by checking executable in the Configure SSIS Logs dialog (menu SSIS --> Logging), next add log on the Providers and Logs tab, then go to the Details tab and select appropriate events (OnProgress and OnError for example). You can click Advanced button and select what columns do you want in your log.

best regards
Grzegorz
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question