importing to SQL Server from .txt

  • Using SSIS, I'm importing a .csv flat file source having 7 columns into a SQL Server table destination. To avoid any problems loading this data I have set the destination columns for every single column to varchar(max).

    Still, columns 2 and 3 are causing the following error:

    [Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Counter" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    [Flat File Source [2]] Error: The "Flat File Source.Outputs[Flat File Source Output].Columns[Counter]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[Counter]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    I've redirected records to another oledb destination whenever columns 2 and 3 encounter failure. After examining the errored rows I don't see anything wrong going on for the data that would fall into the 2nd and 3rd columns.

    I have no idea what is causing ssis to fail on those records. What am I looking for in the rows that errored?

    Thanks.

  • Probably some unicode character featuring in one of the rows from those columns.:unsure:

  • my first reaction would be line terminators

  • regarding prior reply about Unicode - I'm not getting error pertaining to Unicode/non-Unicode and 80,000 rows ARE transferring.

    How would I determine if it's line terminators?

  • KoldCoffee (3/24/2014)


    regarding prior reply about Unicode - I'm not getting error pertaining to Unicode/non-Unicode and 80,000 rows ARE transferring.

    How would I determine if it's line terminators?

    So does it always fail on the same row?

    The fact that your destination columns are varchar(max) does not mean that columns of any width will pass through your data pipeline. You need to trace the path of the data, from flat file source to destination, using the Advanced Editor to view the data types that SSIS is using as the data passes through the pipeline.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You need to trace the path of the data, from flat file source to destination, using the Advanced Editor to view the data types that SSIS is using as the data passes through the pipeline.

    Phil, here's the tracing:

    Output for Counter on FF Source

    External Columns:DT_STR

    Output column: DT_STR

    Output for Counter on Destination OLE DB

    External Columns: DT_TEXT

    Input Columns: DT_STR

    I have no data flow tasks between the FF Source and the OLEDB destination, yet for some reason the data type flips to DT_TEXT.

    Have you seen this before and what do to about it?

  • I've added a Data Conversion Task between the FF Source and OLEDB Destination for the Counter column, to make sure that it is passing as Data Type string[DT_STR] to OLEDB destination varchar(max) column, but still get this error:

    [Data Conversion [2]] Error:

    The conversion returned status value 4 and status text "Text was truncated or one or

    more characters had no match in the target code page.".

    It's coming into OLEDB destination as text stream [DT_TEXT] instead of what I set the Data Conversion Task to do, which is output Counter as DT_STR.

    Ie 'External' Counter column on OLEDB destination task is DT_Text. I believe external means what's coming from the previous data flow task (in this case the Data Conversion Task), and that the 'Input' column what is passed forward?

    Another thing I tried was to alter oledb destination data type for this column to fixed width CHAR(500), but this also made no difference.

    Any ideas on what may be causing the truncation error?

  • Without actually looking at the bothersome data, I'm running out of ideas on this one. It sounds like you should be able to narrow down roughly where the problem lies and view the data in a decent text editor to see what's going on.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I thought I'd share what solved this issue.

    In the Flat File Connection Manager Editor (r-click and select Edit on the Connection Manager itself, not the Flat File Source Data Flow Transform), under Advanced, you can change the size of the property 'OutputColumnWidth'.

    For each column I set mine to 500 and I stopped getting truncation errors. I didn't know up until now that a precedent for the width of each column was set within the Connection Manager, or that it could be edited!.

  • KoldCoffee (4/9/2014)


    I thought I'd share what solved this issue.

    In the Flat File Connection Manager Editor (r-click and select Edit on the Connection Manager itself, not the Flat File Source Data Flow Transform), under Advanced, you can change the size of the property 'OutputColumnWidth'.

    For each column I set mine to 500 and I stopped getting truncation errors. I didn't know up until now that a precedent for the width of each column was set within the Connection Manager, or that it could be edited!.

    As per my very first post on the topic 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • No Phil, that is not correct. You wrote to go to Advanced Editor of Flat File Source. But you didn't say to go to the Connection Manager for the Flat File Source. Very important distinction for anyone else who may read that.

    This was NOT solution:

    ....from flat file source to destination, using the Advanced Editor to view the data types that SSIS is using as the data passes through the pipeline.

    This was solution:

    from Flat File Connection Manager, Click Edit, and go to Advanced and evaluate that the size of the property 'OutputColumnWidth' accommodates the size of the input.

    Flat File Source and Flat File Connection Manager are two different things:-)

  • KoldCoffee (4/9/2014)


    No Phil, that is not correct. You wrote to go to Advanced Editor of Flat File Source. But you didn't say to go to the Connection Manager for the Flat File Source. Very important distinction for anyone else who may read that.

    This was NOT solution:

    ....from flat file source to destination, using the Advanced Editor to view the data types that SSIS is using as the data passes through the pipeline.

    This was solution:

    from Flat File Connection Manager, Click Edit, and go to Advanced and evaluate that the size of the property 'OutputColumnWidth' accommodates the size of the input.

    Flat File Source and Flat File Connection Manager are two different things:-)

    OK, I see your point. It was not as precise as it could have been.

    But notice how I used lower case for 'flat file source' and 'destination' - also very important, at least in my head (:-)), because without capitals they are not defined terms - just a generic source (which happens to be a flat file) and some destination.

    My intention was for you to trace the entire data path, from beginning to end, which includes the definition of the file. My apologies.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Ooooh, I didn't interpret the lower case f's as intended 😉

    Phil, you're really a great resource on these forums and have helped a huge amount in my past three years. I won't take that from you:-)

  • KoldCoffee (4/9/2014)


    Ooooh, I didn't interpret the lower case f's as intended 😉

    ..along with >99% of others, I would guess 😀

    Phil, you're really a great resource on these forums and have helped a huge amount in my past three years. I won't take that from you:-)

    Thank you very much!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • +1 from me

    Phil, Gail and Koen are some of the most generous and helpful forum members on any board I visit and their opinions and advice have saved (or earned) me hundreds of pounds and weeks of work and their contribution is rarely recognised.

    In general this board is the most friendly and active technical forum I use with very little flaming and enduring patience for noobs. Creds go out to all the users. Other folks worthy of a mention here include Lynne Pettis and even Joe Celko (whom we love to hate)

    If any are going to SQL Bits in Telford, Shropshire in June, I would feel honoured to buy them a drink.

    Aaron

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply