FAQ: How do I connect to Excel 12 in SQL Server Integration Services (SSIS)

This question has been coming up a lot internally lately, short answer is:

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 8.0;

Note that the provider is no longer JET but the new data engine from the office team and that you need to set the extended property for the right version. However one of the answers to the question on the internal support alias included a pointer to a very useful wiki, I'll include that reference verbatim:

"I highly encourage you to share your sample and working connection string for this case by updating the article on Office (2007) connectivity here:

https://ssis.wik.is/Data_Sources/Microsoft_Office_(2007)

BTW, our partners already started advertising their SSIS connectivity solutions there as well, you might want to check to see the full list of SSIS connectivity options here :

https://ssis.wik.is/Data_Sources

Update: Should probably be the following:

 Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;