SSIS Auto Converting Dates to Strings

Question

Afternoon, All -

I have a really annoying problem.

I have an Excel file I'm trying to import into a SQL table that has a column of dates, but the vast majority of the cells in the column are blank.

What happens is SSIS, seeing the many blanks in the import data, converts the field to DT_WSTR. I've gone into the Advanced Editor to set the field to DT_DATE, but it refuses to stick.

How do I force this thing to import the field as a date and not convert it to a string?

Thanx in advance for any assistance.

Answer 1

You should try to use Data Conversion or Derived Column transformation right after SSIS Excel Source Adapter.

EDIT
You can try a different approach. By using Microsoft ACE OLEDB Provider. There are three versions of it: 12, 15, and 16.

Please try the following in SSMS.

SELECT *  
 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
   'Excel 12.0 Xml; HDR=NO;  
    Database=c:\yourPath\dada.xlsx',  
    [Sheet1$]);  

It is very easy to check if 64-bit Microsoft ACE provider is installed and what version.
In SSMS:

EXEC master.sys.sp_MSset_oledb_prop;  

You should see it like below:

EDIT #2
You may try to change the following registry entry from default 8 to 0:
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

It will force ACE OLEDB Provider to scan the first 16384 rows of Excel file instead of the first 8 (default) to guess its data type.

Answer 2

Hi @Adam Quark ,

1.We can choose the columns and convert the data types in Data Conversion Transformation.

2.Or we can use (DT_DBDATE)Birthdate expression in Derived Column Transformation to change data types of columns.

Please refer to the following pictures:

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 3

Excel has some quirks that you just have to work around...in this case the problem is either the format of the field or the fact there are 'NULLS' in the field. The only way I have found to work around the issue is to force the data type to a string in the connection manager - and then parse it using either a derived column or directly in SQL Server.

If the format is a valid date format (e.g. MM/DD/YYYY) then SSIS can parse it correctly. If the format is YYYYMMDD then SSIS fails - instead it expects the format to be YYYY-MM-DD.

And if you have NULL values - you have to check for those values before attempting to convert them to an actual date in SSIS, so it just becomes much easier to import as a string...format as YYYY-MM-DD (or MM/DD/YYYY) and pass that string (or NULL) to SQL Server (into a column defined as a date/datetime/datetime2/datetimeoffset). SQL Server can natively convert the string to the date as long as it is a valid date string.