how to redirect rows that are not having same data type as expected to other destination using ssis?
Saturday, December 08, 2012 2:59 PM
I have requirement like I have csv file which contains some columns with datetime records.The file is coming from the shared location,in the file in datetime column records some varchar records exists,now my requirement is i have to redirect rows whicever are not datetime to other excel destination.And that file should go to the client as attachment through email.
OrderID Ordername ShippingDate
1001 abcd 2012-06-12
1002 abgt UPS
1003 ayteu 2012-07-08
1004 sample OTHER
From the above sample data,i need the file whose records for shipping date column are not date records.
Could any one guide me on this requirement ASAP,this is very urgent requirement.
Saturday, December 08, 2012 4:04 PM
If you want to redirect the entire row then do this:
1) define shipping date as a date in your source file connection
2) Specify redirect row on error for that column
3) Attach the error output to a flat file destination - write the FlatFileSourceErrorOutput column to the file
Chuck Pedretti | Magenic – North Region | magenic.com
Saturday, December 08, 2012 4:12 PM
In the DFT make use of Data conversion transformation. Put an excel destination and pull data conversion error output to it, choose redirect row in configure error output. In the data conversion make use of DT_DBTIMESTAMP as datatype. In the control flow put send mail task and in the attachment put the error file that you redirected inside DFT.
Refer these before implementation
Monday, December 10, 2012 12:41 PM
Thanks all for your replies.Now my requirement is I have to send email with error descriptio whenever the job is failed.
Could any one guide me on this?
Monday, December 10, 2012 1:00 PM
You can configure OnError event handler and in that put send mail task and captured error message from System::ErrorDescription variable. Or you can connect the error output from your DFT to send mail task and do the same.
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Tuesday, December 18, 2012 8:02 AM
Tuesday, December 11, 2012 6:16 AM
Thanks for your reply.I have configured send mail task in on error event handler to send mails.I have used System::ErrorDescription variable in expressions tab,
instead of getting single mail I am getting two different mails with two different errors(i.e one error divided into two parts) to my inbox which I supposed to get single .
Could you let me know where I am going wrong?
Tuesday, December 11, 2012 4:01 PM
I know this, what you can do is you can concatenate error message in OnError event handler using script task using other variable and use this new variable in send mail task. Then do as I suggested in previous post.
Or you can connect the error output from your DFT to send mail task and do the same.