Handle Bad Rows in SSIS

Question

I have a few different files which I load with ssis package. text qualifier is " and column delimter is ,  

There can be some bad values some times in files. And I used redirect row for x.csv file and it is working but when I tried same task for y.csv file , it don't work.  Any one has idea about that weird issue?

Thanks.

---

Answer 1

What is the error message ? Is it both files having same format?

Is it row delimeter same for both the files. i.e CR/LF ?

---

Regards, RSingh

Answer 2

Please post the error.

Wild Assumptions: your file may not have any bad row, or there can be any other delimiter in that file. Check and revert back.

---

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

Answer 3

You cannot use two files for the bad rows in the same tasks.

But you can use two files connection, and change the destination file in your CSV task.

Answer 4

I have embedded  comma (,)  character in the file which same with column delimiter.  Even I use text qualifier SSIS , it is failing. I don't want fail. I just want to keep that row into logs and continue with right ones. But It is not able to do it. I use redirect row and I used some thing for a different package with same tasks on a different file and with same format. It is working for it but don't work now. I got erro code -1071607675 on file which it worked. But error code is different now. I think redirect rows only can cacth error code is : -1071607675. It is not able to catch different errors.

"some text","some text 1","some, text 2",""

---

www.mehmetarslanx.com

Answer 5

The first thing you should do in this case is try to swap the source files in both he packages. (Say from package2 which is failing to package1).

Check if package1 is failing now. If it is then there is a problem with your file. Debug it. If Package2 is still failing then surely there is a problem in your package2. Debug it.

For more details, kindly post a snapshot of both the packages and some sample rows from both the files.

---

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

Answer 6

This error number is not related to comma appearing within a string. For that you have specified text qualifier ". There shouldn't be any issue related to comma.

The error code –1071607675, means that a data truncation error has happen. Check the size of the destination columns. Refer this link to decode the error code http://blogs.msdn.com/b/helloworld/archive/2008/07/25/how-to-decipher-understand-ssis-error-code.aspx

---

Regards, RSingh