error import flatfile to sql server

  • muralikrishna37

    Ten Centuries

    Points: 1233

    Hi all,

    i am using sql server 2005 import and export wizard to import flatfile data into sql server.in flat file "Advanced Tab" i taken "suggest Type" for

    converting datatype.but it didnt import into sql server it show below error

    • Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "QMS Bar Code" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    (SQL Server Import and Export Wizard)

    • Error 0xc0209029: Data Flow Task: The "output column "QMS Bar Code" (100)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "QMS Bar Code" (100)" specifies failure on error. An error occurred on the specified object of the specified component.

    (SQL Server Import and Export Wizard)

    • Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Administrator\Desktop\samples.csv" on data row 2.

    (SQL Server Import and Export Wizard)

    • Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - samples_csv" (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.

    (SQL Server Import and Export Wizard)

    • Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.

    (SQL Server Import and Export Wizard)

    • Error 0xc0047039: Data Flow Task: 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.

    (SQL Server Import and Export Wizard)

    • Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.

    (SQL Server Import and Export Wizard)

    Note: what is this "QMS Bar code" i am getting diffent errors to import this file with changing data type.

  • milmish

    Valued Member

    Points: 68

    Here are couple of suggestions:

    - first why don`t you use data conversion tool between source and destination;Set all varchar fields to DT_STR

    - also open your flat file in excel and autofilter to see if there are any values in that field that are notwhat they should be,

    -check if there are any NULL values in the field where the import fails.

    Hope this helps:)

  • skeleton567

    SSCarpal Tunnel

    Points: 4941

    For what it's worth, here's what I've found regarding the truncation errors:

    I have been trying to import two different types of data, my financial records exported to .CSV from Quicken, and my digital music library tag data exported from audio files to .CSV.  I spent far too much time trying to use the SSMS import wizard to no avail.  But when I finally gave up and used SSIS, magically the same files imported to the same tables without error. 

    Using Notepad++ I had scrubbed the music tag files of non-ascii characters which I suspected of causing the problem, but that did not cure things in the import wizard.  Further, I don't download data into my financial records, but do all the entry manually.   I have no idea what the differences may be, but the SSIS process fixed things for me for both data sources. 

    Rick
    Simplicity is the ultimate sophistication.
    - L. DaVinci

  • skeleton567

    SSCarpal Tunnel

    Points: 4941

    For what it's worth, here's what I've found regarding the truncation errors:

    I have been trying to import two different types of data, my financial records exported to .CSV from Quicken, and my digital music library tag data exported from audio files to .CSV.  I spent far too much time trying to use the SSMS import wizard to no avail.  But when I finally gave up and used SSIS, magically the same files imported to the same tables without error. 

    Using Notepad++ I had scrubbed the music tag files of non-ascii characters which I suspected of causing the problem, but that did not cure things in the import wizard.  Further, I don't download data into my financial records, but do all the entry manually.   I have no idea what the differences may be, but the SSIS process fixed things for me for both data sources. 

    Rick
    Simplicity is the ultimate sophistication.
    - L. DaVinci

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

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