I am trying to import CSV delimited data from a Flat File into an SSMS database using the Tasks, Import and Export wizard.

The column is designated as a date, the CSV data looks like a date, but i get conversion errors.

Can anyone tell me why this is happening?

It has to be a data because is it linked to an Access database through linked tables.

Error (ixc02020c5: Data Flow Task 1: Data conversion failed while converting column "EFFECTIVE_DATE" (118) to column 'EFFECTIVE_DATE"(322). The conversion returned status vlue 2 and status text "The value could not be converted because of a potential loss of data". (SQL Server Import and Export Wizard)

Thank you in advance for any help you can gi

After selecting Source and Destination when you move next in wizard, you should see "Edit Mappings" button. Click on it and map data types appropirate for your destination.

Could you post sample data that you have in CSV or at least Effect Date column sample values?

That is correct, all the dates are mapped as dates so that's not the problem.

Again, could you provide sample data for your Effective_date column? How date looks like?

Is it in 03/25/2010 format?

DOB, Effective date and Termination date are all the same and they all look lik that and are all giving me the same error.

Any of these 3 dates columns have either blank or invalid date and when SSIS tries to convert to date; it fails the package.

For above columns,

• You can make sure that all date columns have proper dates
• OR You can set On Error: Ignore so that it ignores the source input value and stores NULL for that record in SQL
• OR You can save the package in SSIS first and then use Derived Column Transformation and use expression like IF col is blank/invalid; set some default date or else correct date as it is
• OR without mapping it to DATE, you can map to nvarchar and store in SQL.

I've allowed for nulls.

I can't Ignore because they need those dates as dates for reporting parameters.

I could easily write a VS application that will load the file provided the database will accept the nulls.

I've allowed for nulls.

I can't Ignore because they need those dates as dates for reporting parameters.