August 17, 2007
Ignoring SSIS Data Conversion Failures
Problem: In SQL Server Integration Services (SSIS), you're trying to import from a data dump from another database system that has different data types from SQL Server. While the conversions look pretty straigh forward, you get failures of the type "The value could not be converted because of a potential loss of data.". But despite the mismatch data types, you can't see anything wrong.
Background: In my case I am converting from a Paradox dump. Paradox has an integer type whose length comes out to 11 digits, whereas SQL Server's int type is 4 bytes (max 10 digits). However, inspecting the data, all of the numbers I wanted to convert where less than 10 digits. But in spite of this I get errors when implicitly converting (copying directly from my file source to my OLE DB destination) or explicitly converting (using the Data Conversion or Derived Column tasks).
Solution: Ignore the errors! I'm an OO programmer, not an SSIS guru, so I can't say this is the ideal solution. But it works for me. I know — without doubt — that my data will convert cleanly. So I converted my fields with the Data Conversion task, and set the Error OUtput to "Ignore failure" for both Errors and Truncation (not sufficient to just do Truncation).
Comments