Date Format Problem Using Ssis For Excel Into Sql Server

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

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";