0

We input data from CSV file and sometimes decimal columns can be null. SSIS DT_DECIMAL or DT_NUMERIC seems not to be accepted null, got -1071607689, DTS_E_OLEDBDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE error.

May I ask we need to use DT_STR column instead, or is there any required setting for it? Thanks for all of your advice.

Sachiko
  • 808
  • 1
  • 12
  • 31
  • 1
    Try [this](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a5f7690-957a-4650-b1e1-e02c266cffaf/inserting-null-values-from-excel-to-the-decimal-column-in-database-data-conversion-ssis?forum=sqlintegrationservices) solution or answer on [this](https://stackoverflow.com/questions/38380034/ssis-data-conversion-from-csv-source-column-null-to-db-destination-decimal18-2) question. – Marko Ivkovic May 06 '21 at 11:17
  • 1
    What type is the csv column in the csv connection manager? – Jayvee May 06 '21 at 11:33
  • 1
    That error is for an OLEDB Destination, and it is not complaining about a constraint violation (NULL), it seems some type of conversion error. I would suggest leaving it as a string and cleansing the values in SQL – Mark Wojciechowicz May 06 '21 at 21:16
  • @Marko Ivkovic Thanks a lot for your useful information !! Finally I understood why keep null options at OLEDB can't work as expected -- this case is not null, just empty. I'll change it as varchar column. Have a wonderful weekend :) – Sachiko May 07 '21 at 22:36
  • @Jayvee Many thanks for your good question. It was DT_STR. And it convert from empty value to 0, if I set it as DT_NUMERIC (scale 0). Amazing. I never know it. Have a wonderful day:) – Sachiko May 07 '21 at 22:52
  • @Mark Wojciechowicz Appreciated your valuable advice. Yes, you're right. I'll change it as varchar at Table Column (OLE Destination) or change as NUMECIC at csv connection manager (it converts empty value to 0 though). Wonderful day for you :) – Sachiko May 07 '21 at 22:59

0 Answers0