CODEWITHSUNDEEP
c#.netexcelssis
RPLM
RPLM

Reputation: 1

Reading dates from an excel file in SSIS. SSIS reads dates in a different format

An excel file has all the dates formatted as MM/dd/yy, I read it in SSIS via c# script, but while looping through the records I notice that some dates are read as dd/MM/yy. Somehow either Excel or SSIS are messing with the date format. Since some of the dates are correctly formatted as MM/dd/yy I am not able to find out which are correctly formatted and which are not. Is there a way to ensure the Excel sheet is read in a specific date format?

I have tried changing the configuration of IMEX to 1 in the connection string. Also tried in the code to identify the dates, but it is impossible since, 01-02-2019 could be January 02 or Feb 01.

The connection string I am using looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=" + "\"" + "EXCEL 12.0 XML; HDR=NO; IMEX=1" + "\"";

I open the excel file in the script and loop through the rows.

The excel has dates like these:

When I read it in SSIS I get these results:

As you can see the date format changes, for some rows.

Is there a way to ensure the format in all read dates?

Upvotes: 0

Views: 213

Answers (1)

Inus C
Inus C

Reputation: 1551

Excel is mostly the culprit. It forces its own data types on SSIS. If you can convert that column to text, it is a good start. (but mostly it is user input right? so probably not)

The problem could be that if the Regional Settings on machines (server/users) is not the same, the format will not be the same as Excel is very much Regional setting driven. (But not so much relevant if this is all on the same machine)

The second problem I have seen is SSIS converting to a DATE is not consistent. In most of my Excel date columns in SSIS, I convert the column to Unicode and then build it up with in expression using SUBSTRING and +. (I though it was stupid until it resolved the issue most of the time. So I have been sticking to it.)

Thus my suggestion, build up the Date you want with String Functions.

Upvotes: 0

Related Questions