July 29, 2009 at 12:11 pm
Hello I have created a package which will move a data from sql1 table to sql2 table. And I have created an error output flat file, where the bad records which are not compatible are loaded here(flat file). In this senario poackage will succeed by skiping bad rows.
Now I need configure a logic, if a bad record is more than 50 then the whole package should fail.
How can I do this.
Here I will explain my data flow,
oledb source-oledeb destination-flat file destination, here flat file destination is a connector for oledb destination error output.
July 29, 2009 at 1:09 pm
wow seems no one have any idea about my question, its pretty simple, just need to create threshold for error ouput file, if an error is more than 50 then package should fail.
July 29, 2009 at 3:49 pm
In the properties for your Data Flow Task, there is a property called MaximumErrorCount. Set this to the number of errors you want to allow before failing the Data Flow Task.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 30, 2009 at 1:35 pm
that didint work at all, i checked 50 on maximum error count and run the package, package run successfully by dumping more than 50 errors in my output flat file.
July 30, 2009 at 2:02 pm
Allen here I will explain inmore detail what I am trying to do.
I need to create a package which should move a data from sql1 table to sql2 table. And I need create an error output flat file, where the bad records which are not compatible are loaded here(flat file). In this senario package will succeed by skiping bad rows. More over as soon as package succedd and if there are errors in output file then it should email me that output file.
Now I need to configure a threshold, if a bad record is more than 50 then the whole package should fail.
How can I do this.
Here I will explain my data flow,
oledb source-oledeb destination-flat file destination, here flat file destination is a connector for oledb destination error output.
In top of that I had created a sendmail task on event handler with option 'on error' but I dont get any email even if it writes an error on output flat file.
but if i change an option on event handler with 'post execute' then I get an email after the execution of package
July 30, 2009 at 2:08 pm
Allen here I have attached a screnshot of my package it should give u an idea, where I am wrong.
SO my main motive is to create a package which will transfer a data between 2 sql server, incase if an error comes then it whould skip that error and load a data and send an email to me with that error.
If an error exceed more than 50 then whole package should fail.
thanks
July 30, 2009 at 2:44 pm
Here's an idea:
From your error output, route that to a Row Count transformation. This will give you the # of error rows in the pipeline. From there, add the Row Count variable into your data flow as a derived column. Use a Conditional Split to check the value, if the value is 50, send the output to a OLE DB Command transformation. Inside the OLE DB Command, use the RAISERROR command to error out the step. Make sense?
July 30, 2009 at 2:49 pm
It makes sense now, i will try to do that, but omy other question how do i configure send mail task, in case if i get less than 50 error then it should send me an email with that error output file
July 30, 2009 at 2:56 pm
I removed the flat file destination from my data flow, so i route error output to row count, but it didnt give me the # if error rows. well what do u mean by rows in pipline. and how to add row count variable on row count transformation.
July 30, 2009 at 3:00 pm
abhisek karki (7/30/2009)
It makes sense now, i will try to do that, but omy other question how do i configure send mail task, in case if i get less than 50 error then it should send me an email with that error output file
The Send Mail task is a control flow componet. You'll need to connect this downstream from your dataflow. You can use the RowCount variable to conditionally execute the Send Mail task.
July 30, 2009 at 3:11 pm
I will appriciate if you can send me the screnshot of the data flow to my question, I am bit confused how to embade row count, derived cloumn and conditional split with me oledb source and destination. hope u had gone thorugh my data flow screnshot. if not here I have attached my dataflow screnshot.
thanks
Abhisek
July 30, 2009 at 3:13 pm
abhisek karki (7/30/2009)
I removed the flat file destination from my data flow, so i route error output to row count, but it didnt give me the # if error rows. well what do u mean by rows in pipline. and how to add row count variable on row count transformation.
I don't think you want to 'remove' your flat file destination. You'll just add a few steps in between where you first start down the error path and the flat file destination.
Data flow tasks process sets of rows at a time. The green/red arrows that show the logical path that those rows flow through is called the pipeline.
To get the Row Counts, you need to first create a variable (package level if you want to use it to kick off your e-mail) to hold the value. Name it ErrorRows, RowCount, ErrorRowCount, or whatever you choose. In the Row Count transformation, set the VariableName property to use this variable.
July 30, 2009 at 3:16 pm
abhisek karki (7/30/2009)
I will appriciate if you can send me the screnshot of the data flow to my question, I am bit confused how to embade row count, derived cloumn and conditional split with me oledb source and destination. hope u had gone thorugh my data flow screnshot. if not here I have attached my dataflow screnshot.thanks
Abhisek
Your screenshot does not show any conditional logic where you are creating an error output. Where are you checking the data for errors?
July 31, 2009 at 1:36 am
RowCount transformation gives row count which are passed through that transformation. That is if 50 rows are transfered then after transfer it will set CountVariable =50. It will increment with every row passed.
MaxErrorCount will not work as you have handled that error by passing data somewhere else. MaxErrorCount is for exceptions thrown by SSIS.
OnError event is not getting called as I have mentioned in my previous point.
I will suggest following:
1. add RowCount transformation between your error flow and text file.
2. check rowcount variable through conditional Split
3. if CountVariable 50 then raise error. I am not very sure how to throw exception from here. But I think it should work.
I am not very sure, why you want the package to fail? I think it will be a good idea to let all errors dump in error file and add a send mail task which will inform you about it.
Please let me know if it works.
Thanks,
Rupashri
July 31, 2009 at 7:49 am
john
mine is pretty straight forward, I edited oledb destination and set 'error ouput' option to 'redirect error' by default it would be 'fail component' so this 'redirect error' option would execute the task even if it had error and finally dump those errors at flat file destination.
Viewing 15 posts - 1 through 15 (of 110 total)
You must be logged in to reply to this topic. Login to reply