December 20, 2016 at 8:42 am
I have a package which loads a file to staging table and rename the filename.Now i am trying to work on data quality stuff.Based on the validation rules the bad data records are moved to Error Staging Table and from there adding error codes ,data is moved to main table.Similarly good data is moved to Good staging table .I am filterign out these good and bad records using a query not through package and i will be adding this as a job step after the package runs.
I am making audit table to store filename,count of good data records,count of bad data records,error percentage ,status(Passed or Rejected based on Error percentage..eg:if %>=.5 then reject else passed )
Now for each file load i want to reject the file and not load into database based on error percentage or based on column value "Status" at the end.
Thanks.
December 20, 2016 at 9:23 am
In an execute SQL task get the status of a file to a variable, and in control flow precedence constraint, evaluate expression to check the value is success for the variable.
December 20, 2016 at 9:57 am
Is there a question in there somewhere?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 20, 2016 at 10:26 am
i wanted to stop the file from loading into DW if the status of file is "rejected" and set up error message and notification ,given the criteria above in the first message.
December 20, 2016 at 11:03 am
sarwar.ali490 (12/20/2016)
i wanted to stop the file from loading into DW if the status of file is "rejected" and set up error message and notification ,given the criteria above in the first message.
This is a requirement, not a question. Which part of this are you having trouble with? What can you not do? Notice how the character '?' is used when asking questions.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 20, 2016 at 11:49 am
sarwar.ali490 (12/20/2016)
I have a package which loads a file to staging table and rename the filename.Now i am trying to work on data quality stuff.Based on the validation rules the bad data records are moved to Error Staging Table and from there adding error codes ,data is moved to main table.Similarly good data is moved to Good staging table .I am filterign out these good and bad records using a query not through package and i will be adding this as a job step after the package runs.I am making audit table to store filename,count of good data records,count of bad data records,error percentage ,status(Passed or Rejected based on Error percentage..eg:if %>=.5 then reject else passed )
Now for each file load i want to reject the file and not load into database based on error percentage or based on column value "Status" at the end.
Thanks.
I'm assuming you mean that you want to stop the SQL Agent job after the data from one file has been moved to the staging tables and before loading your final tables. Since you're already planning on counting the records in those staging tables to record in the audit table, couldn't you just have another step in the SQL Agent job that compares the counts and if they "fail" the criteria then do a RAISERROR? I've used that technique to stop a SQL Agent job
https://msdn.microsoft.com/en-us/library/ms178592.aspx
If your SSIS package is handling the multiple files, then this would be a bit more complicated, as you'd have to notify someone of the error somehow (maybe e-mail?), skip the final processing, then continue through your FOREACH LOOP to the next file.
December 21, 2016 at 7:21 am
Thank you.This is pretty close.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply