Importing .xlsx to SQL Server 2008 on Windows Server 2008

After a frustrating morning of scrawling through blog posts and forums, I finally got a solution thanks to this post: http://sqlserver.posterous.com/excel-connections-in-ssis-64-b

Unfortunately, it's still a ridiculous round-about way to do a simple task but at least it works:

1. Create a DTS Package and store it on the SQL Server with you required mappings and the correct credentials (via Studio Managements 'Import Data' tool, I used Windows Authentication throughout and choosing 'Rely on server storage and roles for access controls')

2. Create a Job in with the SQL Server Agent

3. Add the step of executing the DTS package created ('SQL Server Integration Services Package')

4. 'Edit the command line manually' to include ' \86' - this ensures it runs the 32bit driver

This caused a hell of a lot of frustration, and I'm glad to say that for now at least, it's put to rest.