Importing CSV data into SQL Server using the wizard - cryptic error messages...

Question

I'm trying to use the SQL Server Import Wizard to import flat text file into SQL Server.

Seems like this should be pretty simple. The text file is tab-delimited, with nothing unusual about it.

When I preview the data in the wizard, everything looks OK. I've checked the column mappings, and all the data lines up, and the data types all seem OK.

But when I run it, here's the error message I get:

Copying to [dbo].[my_table] (Error) Messages

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
 (SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column "touch4_date" (260) on input "Destination Input" (179). The column status returned was: "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (179)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (179)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - cm_campaigns" (166) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  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 "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)

Some additional detail....

The Flat File only has 22 rows.

In the column that seems to be causing the problem ("touch4_date") - 19 of the rows contain empty values, and three contain the following values:

7/2/2008
8/25/2008
3/12/2009

The column the database is a datetime, and it allows nulls.

One of the strange aspects is that the flat file contains other columns with data in the same format "mm/dd/yyyy" that map to datetime columns, but those don't seem to cause any errors?

Can anyone shed any light on the problem?

Many thanks in advance!

Answer 1

The touch4_date column has a value that can't be cast to your table column type.

Answer 2

try importing the flatfile into a staging table with the column tough4_date as a varchar. then insert into your actual table, but use a case statement.

declare @string varchar(50)

set @string=''



select
    case @string 
     when '' then null
     else @string
    end as String