In 2012 you will use the normal Excel Source instead of the OLE DB Source. OnClientClick.xlsm OnClientClick javascriptMyObject new ActiveXObjectWScript.Shell MyObject.Runfile/// C/MacroEnabledFile. Shown below is the 2012 Source Assistant that you’ll noticed took care of the Extended Properties setting you had to manually do in 2008. As long as you have the before mentioned Access driver you’re ready to go. Guess what? With SSIS 2012 you don’t have to do anything extra! The default Excel Connection that didn’t work in 2008 does work now. If you don’t want the first row of data to be the column header than change this to NO.Ĭlick OK and use an OLE DB Source in your data flow to either select a sheet or query the workbook. Most of this text is self explanatory except the HDR which stands for header. Next click on All to modify the Extended Properties by adding Excel 12.0 Macro HDR=YES. Then provide the path for your macro enable workbook in the “Server or file name property”. SSIS 2008Ĭreate an OLE DB Connection Manager and use the Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider (.12.0). Don’t worry it doesn’t require a server restart.
Both solutions require you have the Microsoft Access Database Engine 2010 Redistributable driver installed so make that your first step before reading on. I thought I’d go ahead and document the solution in both 20 for you. In fact, this is the error message you get after trying to close the Excel connection manager. If you have ever tried this yourself you may have tried using the default Excel provider, which does not work. Recently while working for a client that was running SQL Server 2008 R2 I was tasked with loading an Excel Macro (.xlsm) enabled workbook.
0 kommentar(er)
