Date format problem using SSIS for Excel into SQL Server

Question

I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy.

Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings in dd/MM/yyyy (with Excel 'General' formatting) as well as dates using Excel 'Date' formatting dd/MM/yyyy (with locale 'English (United Kingdom)').

This is just the way it is and I can't expect the users to be able to sort this out themselves.

When looking at the spreadsheet, all of the dates visually appear correct i.e. dd/MM/yyyy.

I am trying to import the values into a varchar column in a holding table in the database. Then I run a stored procedure that copies these values into the proper table which contains a datetime column.

The problem is that the dates that are stored in the spreadsheet and use Date formatting get imported as MM/dd/yyyy into SQL Server and the dates stored as strings are getting imported as dd/MM/yyyy. I have IMEX=1 in the connection string.

Having dates using both formats in the same varchar column is obviously causing a problem when I try to put it into a datetime column, hence

SET DATEFORMAT MDY;    
SET DATEFORMAT DMY;

are of no use.

Does anyone know why the SSIS package would import the seemingly correct dates in the Excel spreadsheet into SQL Server as MM/dd/yyyy anyway?

Is there anyway to force the SSIS package to import the dates as dd/MM/yyyy that will work with this combination of dates as strings and cells with date formatting applied?

Any other ideas?!

Many thanks,

Anthony

Solution 1

I think you have answered your own question. The import of date formatted cells are treated as dates and others as a string. Possibly you SQL server date setting is MM/dd/yyyy and hence the conversion.

Why don't you try adding a data conversion step in you SSIS package and convert everyting in the column into a single format - datetime or string. Then I am sure SQL server will handle all of them the same way.

Raj

Solution 2

What worked for me was to add IMEX=1 to the Excel connection string.

So it will look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder1\Book1.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";