20

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of this problem on StackOverflow and DBA Stack Exchange, without any luck.

I've been struggling with this problem for the past two days (working on it for approximately 7 hours a day), and even after Googling the issue, it appears that no one else are having the same exact variant of my problem.

What am I trying to do?

In SSIS, I am trying to read from a CSV file, and insert the rows from it into an OLE DB database. For that, I have made the simplest setup ever, seen below.

  1. Flat File Source - reads the CSV rows.
  2. Derived Column - currently does nothing (it's just there for experimenting).
  3. Data Conversion - currently does nothing (it's just there for experimenting).
  4. OLE DB Destination - stores the rows in the database.

When I try to run it, it stops executing at my OLE DB Destination with the following error-message.

There was an error with input column "Amount" (187) on input "OLE DB Destination Input" (51). The column status returned was: "The value could not be converted because of a potential loss of data.".

The column that fails (Amount) is currently of type DT_STR. It appears to be the type that I have the most faith in right now.

What have I tried?

  • I've tried using the Flat File Connection's "Suggest Types" function on the column that is failing. This made it recommend the Single byte signed int datatype.
    • Stops at my Flat File Source.
    • The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
  • I've tried using the Derived Column to cast the column into a DT_I4.
    • Stops at my Derived Column.
    • The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
  • I've tried using the Data Conversion to cast my column's value to a DT_I4.
    • Stops at my Data Conversion.
    • The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
  • I've tried changing the length of my DT_STR value in the source and the destination.
    • Stops at the source or the destination depending on the settings.
  • I've tried connecting using an Excel Source connector instead with and without the IMEX=1 added to the connection string. No luck.

My programming sense tells me that I am screwed. I have never ever encountered such a big problem for such a simple thing.

And why is it that I can't just ignore the "potential" loss of data? It's really frustrating to say the least.

My system

It's a Windows Server 2008 R2 machine with SQL Server 2008 installed. The machine itself is fully updated through Windows Update.

Mathias Lykkegaard Lorenzen
  • 579
  • 2
  • 5
  • 13

2 Answers2

10

Ignoring the error

First of all, you should be able to ignore string truncation by going to your Flat File Source, Error Output and then changing "Fail Component" under "Truncation" to "Ignore Error".

Better solution

The real issue could be that the string length inside the SSIS pipeline is still wrong because it got initialized at some earlier point.

You can determine if that is the case by double-clicking the green arrow from your Flat File Source (or after Derived Column/Data Conversion) and choosing "Meta Data". There you can see the length of the field inside the pipeline.

If have often seen that meta data doesn't match the source component anymore, mostly if creating the Flat File Source AND its corrsponding Connection via the Wizard.

My suggestion would be to delete the connection and the Flat File Source and recreate them as I haven't found a way to re-sychronize pipeline meta data with source components.

Oliver Rahner
  • 356
  • 1
  • 5
7

I have run into this a few times and the issue has been a mismatch of data types in the export/import parameters. First thing to do is check those parameters by right click on source or destination, selecting Advanced Editor. Go to Input and Output Properties and check the data type for that column for all items - both External, Output, and Input. I have usually seen a variance here, often times one is Unicode (WSTR) and the other I4 or DSTR.

Mike
  • 86
  • 1
  • 1