Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

put on hold as unclear what you're asking by Paul White, RolandoMySQLDBA, Mark Storey-Smith, RLF, Max Vernon 22 hours ago

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Seems like I had this problem with a value (although pulling from Access) and I had to use a specific code page when I cast the column. I don't have access to the SSIS package anymore to check what I did though. –  Shawn Melton Aug 14 '13 at 14:01
    
What's the data type of the column into which you're trying to insert Amount? –  Jon of All Trades Aug 14 '13 at 14:30
2  
Open your Flat File Connection Manager for the CSV. Go to the Advanced tab and select the Amount column. Take a screen shot and please update your question with this picture. –  billinkc Aug 14 '13 at 14:52
    
Had an opportunity to look at the Connection Manager yet? –  billinkc Aug 19 '13 at 0:32
1  
I do not believe you are screwed but without seeing the definition of your Connection Manager, it's impossible to say. –  billinkc Aug 21 '13 at 3:07

2 Answers 2

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.

share|improve this answer
    
This does not fall under "truncation" though. I already tried it. –  Mathias Lykkegaard Lorenzen Aug 19 '13 at 7:51

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.