SSIS Data Conversion problem

  • Al Sallam

    SSC Veteran

    Points: 239

    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_1 col_2 col_3 col_4

    57618 2 200 char TXT 50 char Text

    64819 33 200 char TXT 50 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

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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: 239

    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

    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: 239

    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

    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: 4547

    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.

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

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