SSIS Data Conversion problem

Question

SSIS Data Conversion problem

Al Sallam

SSC Veteran

Points: 299
More actions
May 24, 2011 at 2:52 pm

#389583

I am having problem converting simple data types in SSIS package that I am building. I keep getting error msg below. I tried different data types in SSIS, still having a problem. is there a source for data conversion types best practice? the scenario that I have is below.
Start*******************************************
Scenario:
Migrate data from the flat file below, to sql server table using SSIS.
Flat file:
col_1col_2col_3col_4
576182200 char TXT50 char Text
6481933200 char TXT50 char Text
SSIS Conversion Task:
col_1 data type:DT_I2
col_2 data type:DT_I2
col_3 data type:DT_WSTR
col_4 data type:DT_WSTR
sql server db table:
col_1 data type:int
col_2 data type:int
col_3 data type:varchar(50)
col_4 data type:varchar(50)
Error msg:
SSIS package "Package.dtsx" starting.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "col_2" returned status value 2 and status text "The value could not be converted because of a potential loss of data.”
Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: The "output column” col_2" (33)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column " col_2" (33)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\Documents and Settings\Alaa Sallam\My Documents\XRD.txt" on data row 26.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown Signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
(1343)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package.dtsx" finished: Success.
***********************************************End
Thanks
Al

Viewing 7 posts - 1 through 6 (of 6 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I thought that DT_I4 is equivalent to int?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Al Sallam SSC Veteran Points: 299 More actions · Answer 2

I tried "DT_I4" and still got error msg. Is there something that I am doing wrong besides selecting "DT_I4"? I will try it again. do you know of a resource where I can see data type equivalence from one source to another; sql, access (jet 4), etc.

thanks

Al

phonetictalk Hall of Fame Points: 3582 More actions · Answer 3

Could it be a problem with the flat file? E.g. a record without enough columns or where the columns are mis-aligned causing text to appear in the integer column...

Leonard
Madison, WI

Al Sallam SSC Veteran Points: 299 More actions · Answer 4

this could be. how should I fix that? I open the flat file in excel and everything looks aligned in a separate column. I have 4 columns in excel.

phonetictalk Hall of Fame Points: 3582 More actions · Answer 5

Honestly my approach would be trial and error. Take the flat file, split it in half and try doing each half separately. If one half works and the other doesn't, take the half that doesn't and split it further until you find a problem record.

Another approach might be to change the flat file so there is only one record in it. If it works, that suggests that some records work and some don't (so it's likely a data problem). If you can't even import one record, then it's something more global about the setup.

Leonard
Madison, WI

cliffb SSCarpal Tunnel Points: 4597 More actions · Answer 6

Try enabling error handling and spit the rows that are failing into a flat file for examination. This will allow you to narrow your investigation to what is causing the issue vs looking at everything.