SSIS data conversion error

Question

Dear gurus,

I have data from a csv file and i took every column datatype as string initailly. After this i want to convert the data into numeric datatype so i used data conversion to do this.Am getting the following error while changing my datatype in data conversion t/m. Could u please help me 

[Data Conversion [2]] Error: Data conversion failed while converting column "Delayed" (301) to column "Copy of Delayed Data Chgs" (55).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Answer 1

Error suggests that some truncation is happening while doing the data conversion. Configure the error op of data conversion to redirect the rows for error and truncation. Click in configure error op and select "redirect row" for error and truncation

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

Hi Nitesh,

While am doing some ETL's am getting some errors, can i configure error output to redirect rows  for error and truncation. Does it create any problem while keeping the ETL in production. If u could please explain about this brief.

Thanks,

Venkat.

Answer 3

Error redirection is used to allow the ETL to continue and capture the error records to a table or file. Once the ETL is done, you can see the error file or table to check if there was any error record and you can send it back to source team or investigate yourself about these records.

Yes, you can easily deploy and run the package with error redirection on production server without any problem.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 4

Hi Venkat,

By default the csv file will provide each column with varchar data type of length 50. If the destination table has column of numeric data type, it would be loaded into the destination column. But, truncation error will certaily happen if the destination column is not able to accomadate the data.

Also, you can convert the destionation column of numeric data type to something like numeric(38,2). It will allow data insertion and  SSIS do implicit conversion from varchar to numeric.

Thirdly, as Nitesh suggested redirect error rows to flat file or error log table is good practice that doesnt hinder anything on prod server when packages are deployed.

---

Thanks, hsbal