Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

importing to SQL Server from .txt Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 12:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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.
Post #1553867
Posted Monday, March 24, 2014 12:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:39 AM
Points: 470, Visits: 483
Probably some unicode character featuring in one of the rows from those columns.
Post #1553874
Posted Monday, March 24, 2014 3:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
my first reaction would be line terminators
Post #1553925
Posted Monday, March 24, 2014 4:04 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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?
Post #1553940
Posted Tuesday, March 25, 2014 4:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,167, Visits: 12,017
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1554366
Posted Wednesday, March 26, 2014 4:19 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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?
Post #1555203
Posted Tuesday, April 1, 2014 8:40 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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?
Post #1557300
Posted Thursday, April 3, 2014 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,167, Visits: 12,017
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1558066
Posted Wednesday, April 9, 2014 4:05 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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!.
Post #1559856
Posted Wednesday, April 9, 2014 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,167, Visits: 12,017
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 :)



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1559888
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse