2

I have Data Flow task to loop through Excel files in folder. In Excel Source I have date field set to Redirect Row. Wanting to send entire row that has date errors to another excel file with same column headings. I have dragged red line to Excel Destination. Even though there are erroneous dates in the source excel file, the component does not fail or redirect row. It loads the data with all the records in the OLE DB (SQL table), and puts null in the table with the erroneous date. What am i missing?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ginger22
  • 147
  • 2
  • 12

1 Answers1

0

Try changing the Excel Source ValidateExternalMetadata property to True, if it still not working just instead of using Excel source error handling, add a Data Conversion Trasnformation and convert the text field to DT_DATE or DT_DBDATE and use the Trasnformation Error Output to redirect rows.

Concerning your question

What am i missing?

After many experiments on Source metadata validation, it looks like that it doesn't works all the time since it depends on the source provider properties, as example SQL Server database engine works fine since it is a relational database and force columns data types constraints while flat files and excel are not.

Helpful links

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Validate External Metadata property was already true. I added the data conversion, and I selected the date field as date. It names the alias as copy of date. When i click on the error file to map it, the alias field does not show up so I can't map it to the destination date field. ? So it just runs like before and adds all of the rows to the table with null in date field that is bad. Any ideas? – Ginger22 May 12 '19 at 21:50
  • @Ginger22 the error output redirect the field before converting it to date type. If you are receiving null then the issue is not related to date conversion, it is related to excel oledb, since if the columns contains different data types the oledb provider will read only the values of the dominant data types and convert all others to `null`, you need to search for `Excel columns with mixed data types ssis issue`. – Hadi May 12 '19 at 21:56
  • @Ginger22 check the following link https://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/ – Hadi May 12 '19 at 21:57
  • @Ginger22 https://stackoverflow.com/questions/542497/ssis-excel-import-forcing-incorrect-column-type – Hadi May 12 '19 at 21:58