none
Data Import Wizard- Code page 1252 ANSI Error: The data conversion for column "Column 1" returned status value 4

    Question

  • HI All,

    I was trying to import a csv file in sql 2005.  BUt It giving following error.

    My csv files contains following type of data:

    UKPNBZT02|MS_TCP_Loopback_interface|NETniBytesTotalPerSec|02/09/2013 00:03:53|281.753
    UKPNBZT02|MS_TCP_Loopback_interface|NETniBytesTotalPerSec|02/09/2013 00:08:58|294.974

    UKPNBZT02|MS_TCP_Loopback_interface|NETniBytesTotalPerSec|02/09/2013 00:13:58|1109.55

    UKPNBZT02|VMware_Accelerated_AMD_PCNet_Adapter_-_Network_Load_Balancing_Filter_Device|NETniBytesTotalPerSec|22/09/2013 23:43:59|64456.3
    UKPNBZT02|VMware_Accelerated_AMD_PCNet_Adapter_-_Network_Load_Balancing_Filter_Device|NETniBytesTotalPerSec|22/09/2013 23:48:59|74633.3
    UKPNBZT02|VMware_Accelerated_AMD_PCNet_Adapter_-_Network_Load_Balancing_Filter_Device|NETniBytesTotalPerSec|22/09/2013 23:53:59|56029.1
    UKPNBZT02|VMware_Accelerated_AMD_PCNet_Adapter_-_Network_Load_Balancing_Filter_Device|NETniBytesTotalPerSec|22/09/2013 23:58:59|49784.6

    Messages
    Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
     (SQL Server Import and Export Wizard)


    Error 0xc020902a: Data Flow Task: The "output column "Column 1" (14)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (14)" specifies failure on truncation. A truncation 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 "D:\UKPNPatrolData\Patrol_NETWORK_1.csv" on data row 6016.
     (SQL Server Import and Export Wizard)


    Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Patrol_NETWORK_1_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.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "SourceThread0" has exited with error code 0xC0047038.  There may be error messages posted before this with more information on why the thread has exited.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED.  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.  There may be error messages posted before this with more information on why the thread was cancelled.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0047039.  There may be error messages posted before this with more information on why the thread has exited.
     (SQL Server Import and Export Wizard)

    Also it is not allowing me to edit width option while importing.

    Could you please help me???


    Thanks and Regards, Bala


    Saturday, September 28, 2013 2:07 PM

All replies

  • Hi Bala,

    You have five columns in your CSV file. The data types for them should be as follows:

    1. col1 - varchar(500) - find the appropriate length for this column (DT_STR, length 500)
    2. col2 - varchar(500) - find the appropriate length for this column (DT_STR, length 500)
    3. col3 - varchar(500) - find the appropriate length for this column (DT_STR, length 500)
    4. col4 - datetime (DT_DBTIMESTAMP)
    5. col5 - decimal(10,3) (DT_DECIMAL)

    This is configured in the choose data source editor.

    The destination table can be created by import/export wizard or you can create it prior to running the wizard. If using wizard to create the destination table then click on Edit mappings button on Select source tables and views step of the wizard. When Column mappings windows pops up click on Edit SQL. You should get something like this:

    CREATE TABLE [dbo].[csvproblem]
    (
    [Column 0] varchar(500),
    [Column 1] varchar(500),
    [Column 2] varchar(500),
    [Column 3] datetime,
    [Column 4] decimal(10,3)
    )

    You can give your columns some user friendly names of course.

    If you have any further problems or if your datetime columns converts its values incorrectly I advise you to create a SSIS package (last step of the wizard) and then open it in Visual Studio. There you can manually edit source and destination configurations and use Derived column transformation to transform datetime column in csv to datetime data type in SQL server table.


    HTH, Regards, Dean Savović

    Sunday, September 29, 2013 11:59 AM