Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


importing to SQL Server from .txt


importing to SQL Server from .txt

Author
Message
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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.
SQLCJ
SQLCJ
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 Visits: 576
Probably some unicode character featuring in one of the rows from those columns.Unsure
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
my first reaction would be line terminators
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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 ColumnsBigGrinT_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?
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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!.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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 Smile


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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search