February 6, 2010 at 12:39 pm
Hi All,
I am re-directing error rows from an OLE DB Destination to a Raw file destination. Is there anyway to re-direct error rows but still fail the package?
Many thanks in advance.
Regards,
Chris
February 6, 2010 at 10:54 pm
You could do this using a script task. If you include a Row Count Transformation in your error output, and use it to count the rows sent to that output. Then, create a script task to check to see if that row count variable is a nonzero value, and force the package to fail within the script.
I've attached a sample package that will demonstrate how you could accomplish this.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 7, 2010 at 12:54 am
Hi Tim,
Many thanks, this sounds exactly the sort of solution I require.
I have tried to Open your example package, however I get an error 'CPackage:LoadFromXMLfails'.
Could you possibly re-send? I am using SSIS 2005 if that could be causing an issue?
Many thanks.
Chris
February 8, 2010 at 12:43 am
An alternative to check the Row Count in a script is to use a precedence constraint. For Evaluation operation use Expression and check for RowCount > 0.
The precedence constraint is followed by a task that generates an error (e.g. script with Dts.TaskResult = ScriptResults.Failure or an Execute SQL Task like select 1/0).
February 8, 2010 at 6:56 pm
Chris,
Yes, it's probably a version difference - my dev system is SQL Server 2008. Do you have access to a SQL Server 2008 installation? If not, I can send you a couple of screenshots that should help you get started.
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 9, 2010 at 12:33 am
Tim,
I am working with SSIS 2005 and don't have access to 2008 yet. If possible could you send me a few screen shots?
Many thanks.
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply