Phil... where do you hail from?  I ask because I'm giving the "Automating "Excel Hell"" presentation at the Cleveland SQL Saturday on the 1st of February 2020 and would love...

Unzipping files is natively available in .NET, so there should be no need to install additional software.

See here for more info.

Thanks, Eirikur, I've had a look into that in the past, now that you mention it.

If anyone has a handy chunk of code, working something like this (with suitable param...

This is why I moved away from SSIS and ACE for spreadsheets with mixed data (ie numeric and alphanumeric) in the same column, unless I can guarantee the data....

Panic over 🙂 Happy new year to you too.

Thom beat me to it!

microsoft.com vs amis.nl ... I know which source I'm going with.

Jeff, thank you for taking the time to write all of this stuff out.

Importing from Excel using SSIS is almost always a massive pain, so I am going to check...

No it isn't, it's a Window function.

John already provided a link to analytic functions. Had you followed it, you would have seen that DENSE_RANK()...

Another possibility is extracting a DACPAC for the DB in question and building an empty one from that.

OK, so what was the "silly" fix you employed?

I'll also state that it's a shame that you're...

If the procs don't contain DML, what do they contain?

Presumably because NULL is not a value.

Have you considered just using ELSE?

Otherwise, use the extended 'Searched CASE' syntax:

... WHEN Education is NULL THEN 'Unknown'

I do this in (a significant chunk of) C#, using a Script Task, for .XLSX files (as well as CSV files and Access files).

It's not for beginners, but it's not...

If you want a working solution, please provide your sample data in the form of CREATE TABLE/INSERT statements.

Please expand on this:

But when i open and saving that same excel file as .xlsx format it loads without error.

What format was the file in before you opened and saved?