ACE engine and SSIS woes in VS2019. I want to stop incorrectly typed data from just disappearing because ACE makes it NULL. Once the data is in SSIS, I will type the data using Data Conversion and deal with any bad data at that point. The solution to ACE losing data is of course to set IMEX=1 and to include the header row in the data (to provide guaranteed string data) and then use a WHERE clause to exclude the header row. This will set all columns to string in ACE and therefore SHOULD set all Excel Source external columns to string - unfortunately it doesn't.
The problem:
Create an Excel Connection Manager. Don't set the header checkbox. There is no option to set IMEX. Then manually add IMEX=1 to the ConnectionString. This works and you can see it has been added. Create an Excel Source. Select the Excel Connection Manager. Write a SELECT statement to get your data. Unfortunately, preview shows the bad data has been set to NULL and that the IMEX=1 setting has been removed from the Excel Connection Manager.
Did this always happen as I've seen quite a few old solutions that suggest manually adding IMEX=1 and this suggests that it used to work, so is this a regression in recent versions?
Incidentally, once you have linked an Excel Source to the Excel Connection Manager, the Connectionstring of the manager is no longer editable. You can edit it again if you remove the link.
You can add IMEX=1 to the dynamic Expression ConnectionString, but this is not useful at design time when setting up the column datatypes.
One workaround is to add string data to at least 5 of the top 8 rows for the design time Excel file (so when ACE guesses the column datatype it chooses string even without IMEX=1). But it's really hacky and any maintenance developer coming in will likely break the datatypes.
Okay. I'm an idiot. When you add Expressions they override the attribute that they cover AT DESIGN TIME AND RUN TIME. The properties window will update from the expression once the changes are saved. For some reason I thought the underlying property was used at design time and the expression was just used at runtime. I can't believe how long it has taken me to come to this simple conclusion. Anyway, apologies again for being a moron. I'll leave this up in case anyone else is having a funny 5 minutes.
So, basically, change the expression and save and it will change the underlying property value.