how to redirect rows that are not having same data type as expected to other destination using ssis?

Question

Hi ALL,

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.

Sample data:

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.

Regards,

---

sudha

Answer 1

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

Answer 2

 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

http://www.simple-talk.com/sql/ssis/working-with-ssis-data-types/

http://www.c-sharpcorner.com/uploadfile/MIkkykumar/how-to-send-email-using-send-mail-task-of-ssis/

Answer 3

Hi All,

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?

Regards,

---

sudha

Answer 4

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.

Answer 5

Hi Yogish,

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?

Regards,

---

sudha

Answer 6

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.