November 7, 2015 at 4:55 pm
Hi Guys,
Need some help from the SSIS experts out there. I have a legacy code using Execute SQL task in my package. It is creating bunch of temp table and then trying to query against the temp tables. Couple of queries filtering records looking for a date value between 2 other date fields. Once of the date field out of these 3 data fields seems has bad data. In want to redirect the bad data rows so that I can log bad data. I know Data flow containers have option to redirect bad rows but don't know how to handle that in the Control flow container Execute SQL task. Any advice will be appreciated.
November 9, 2015 at 10:10 pm
You have to do it at SQL level only, perform data validation check and move that row to another table which will store bad records.
____________________________________________________________
APNovember 10, 2015 at 2:47 pm
I got to wonder why create the temp tables to then only pass through a subset of those. Maybe the base tables lacks an index but I dont see the benefit gain unless that temp table is re-used in the task.
In any case the SQL task will have IF ELSE logic. In your IF try selecting only those rows that meet the strict validation criteria and dump the rest to an audit table in the ELSE block.
I like the data flow method because I can utilise conditional splits to create a waterfall of checks on the records that were not clean at the first check ( to attempt to fix these in the stream). This would have the benefit of less records for human eyes, if any, at the end of the transfer ultimately going into the failed table.
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply