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

Invalid character value for cast specification Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 4:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:26 PM
Points: 111, Visits: 500
I have a txt file with data as below:

col1,col2,col3

NY,14319,9900

MA,14241,9900

CA,14358,Get WO NE

To load this file, I am using a flat file source and I have declared the data type of all three columns as Unicode String with 500 length in the advance editor..But when I run this package, I am getting an error at the third line:

Invalid character value for cast specification

I get this error at the source adapter only..Can someone please help me out here..

P.S. Not giving a table structure as this a txt file

I am using SSIS 2008
Post #1427081
Posted Tuesday, March 5, 2013 4:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
A guess, SSIS is expecting the third column to be an integer and the text data it found can't be converted.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427083
Posted Tuesday, March 5, 2013 5:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:26 PM
Points: 111, Visits: 500
Thanks for the reply mate..
but I have already mentioned the data type of the columns as Unicode String with 500 length..in the connection manager and in the source adapter as well
Post #1427086
Posted Tuesday, March 5, 2013 6:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
Okay, I gave it my shot in the dark. Unfortunately we can't see what you see so unless you provide us with more information that's about all you will get.

The force may be strong, but it isn't that strong.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427107
Posted Wednesday, March 6, 2013 7:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
What's on row 4?

If you remove that row, does the rest of the file process OK? If not, what else fails?



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 #1427383
Posted Wednesday, March 6, 2013 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:26 PM
Points: 111, Visits: 500
Row 4 is again like below:
CA,93455,9435

I am not able to process the file after row#3 where it is failing in the data flow task..even redirecting the erranous rows fails as the source adapter is struggling to cast the value in the third row
Post #1427465
Posted Wednesday, March 6, 2013 9:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
Can you provide the complete and uneditted error message?


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse