Solved

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

Posted on 2008-10-07
2
1,080 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
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 65

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 35
Sort by Month and Year - SQL 3 23
Recurring Excel Timelime for Veeam 2 34
Query to Add Late Tolerance 10 29
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now