Solved

How to handle Try/Catch error handling in Source task within a data flow

Posted on 2008-10-07
2
1,110 Views
Last Modified: 2013-11-10
Hi All

I have an SSIS package with a number of data flow tasks with OLE DB Source tasks that execute SP's.

I'd like SP to have the ability to return somewhat English-sounding error message if an error occurs within the SP.
Doing this within a SP using a TRY/CATCH block is no biggie, but I'm trying to figure out how to handle this within my SSIS data flow task.

As far as I can tell, with OLE DB Source tasks if there is any difference between what is returned, and what is pre-mapped to an OLE DB Destination task, SSIS will throw a runtime error, with different error properties that what would be trapped by a TRY..CATCH block within T-SQL.

Has anyone pulled this off?

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
aaronakin earned 500 total points
ID: 22664205
Hi jimhorn,

I'm not completely sure I understand your question.  Yes, SSIS will throw the same error that your procedure throws.  Here's a test package to show you if it helps.

If you are wanting to actually catch that error, you're gonna need to use a script component with a try/catch block in it.
Test-Package.dtsx.txt
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 23014537
>you're gonna need to use a script component with a try/catch block in it.
Pretty much the answer I was expecting, as SSIS doens't handle one schema for success, another schema for errors.

Thanks.
Jim
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

626 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