SSIS Error importing Excel Date (truncation error)

Question

I am sorry to post what seems a very simple issue but I cannot find an answer and I am wasting days (not just hours at this point). I am fairly new to SSIS and it is just kicking my backside.

Background:

Pretty straightforward SSIS Package to import an Excel sheet into a Staging table in SQL Server. Since I do not want to mislead anyone by using the wrong nomenclature, I will refer to the Excel source as Excel and the SQL Server table as the Target Table.

This package HAS worked before. However, it is now failing because of data truncation for a Date Column. The Excel Column has been formatted as a DATE (and I have tried a few different format options within DATE). The target column is also a DATE column (NOT datetime). The data in Excel is predominantly empty cells with a few sporadic values. I think the errors started when the dates started appearing in the data (rather than just blanks).

I have tried using the Advanced Editor for both sides (Excel & Target) and tried numerous data type settings all around but I keep getting the same failure. I suspect that it is now pretty messed up with the various tests that I have done.

I have also tried adding a Data Conversion transform for the Date field “date[DT_DATE]” – that did not work. AND, I have tried creating a Derived Column - first based on the Excel column and then on the Transformed column. All of those attempts have failed.

Questions:

1) What is the best practice for importing Excel data into SQL Server for DATE Columns?

2) Since this is two very mature Microsoft Apps (Excel & SQL Server) working together, it seems like it should be simple. This leads me to believe that I must be missing some basic concepts here. Can anyone set me straight?

3) How do all of you get an Excel date into SQL Server?

4) What is the trick for synchronizing columns after making edits?

Thanks for any insights you can provide. Sorry to bother you all with what seems pretty simple.

David

Answer 1

The main problem when importing Data from excel worksheets is that excel is that each column in excel can have multiple data types or formats, so the same column can contains Dates and Numbers and text or dates with differents formats (Some formats cannot be converted implicitly to dates in SSIS).

If all date value are stored as date (not Text), The best practice to Import dates from an excel worksheet is to convert DATE to Number format "0.000000000" (in excel it is called Serial DateTime) from excel or programmatically using a library like Microsoft.Office.Interop.Excel

You can refer to this Link but use the following:

xlCells.NumberFormat = "0.0000000"

Then in SSIS package use a script component to convert it again to Date using DateTime.FromOADate() Function

*Assuming that inColumn is the Date column with a numeric type, add an output column outColumn of type DT_DBTIMESTAMP or DT_DATE and use the following code:

If Not Row.inColumn_IsNull Then

    Row.OutColumn = DateTime.FromOADate(CDbl(Row.inColumn))

Else 

    Row.OutColumn_IsNull = True

End If

Note: When converting column to Number Format, you ignored all formats but still have the date value

• To read more about DateTimes in Excel you can refer to this Link

• To read more about Date time formats that can be implicitly converted to date in SSIS follow SSIS Source Format Implicit Conversion for Datetime

Answer 2

Personally I don't think there is a best practice for excel dates, it is always a pain for me.

If you can format the excel file try changing it to 'Text'. it will import as Unicode and not a date. If not, try and convert the column in a "Data Conversion " task to Unicode

after that is done, you would need to use a "Derived Column" task. Build the date in the format you want.

example for source MM/dd/yyyy hh:mm:ss

Build to be yyyy-MM-dd

SUBSTRING(datecolumn,7,4)+ "-" + SUBSTRING(datecolumn,1,2)+ "-" +SUBSTRING(datecolumn,4,2)

Might be crude, but saves my sanity.

If the date looks something like m/d/yyyy not including 2 values when Jan or something, you will add a few things like this for the month part.

RIGHT("0" + SUBSTRING(datecolumn,1,FINDSTRING(datecolumn,"/"1)-1),2)

Good luck