Recently I updated an old SSIS package due to an upgrade. This SSIS package exports data from SQL Server to Excel on a monthly basis. The Accounting and Finance departments need stand alone copies of data in Excel spreadsheets to do what-if analysis after the month is closed out. The challenge is Excel 2007 is typically a 32 bit program designed for workstations, and a 64 bit SQL Server doesn’t export data to a 32 bit file without some workarounds. After reading numerous articles on the Internet and 3 tries with Microsoft Support, I found the correct setup for exporting data. I have documented the steps as outlined below.
Prerequisite: Microsoft Access Database Engine 2010 Redistributable
Install the Microsoft Access Database engine 2010 for connectivity to the SQL Server. The install is available at: http://www.microsoft.com/en-us/download/details.aspx?id=13255
While one would think you would need the 64 bit version, the x86 install is actually needed due to trying to get a 64 bit OS to work with a 32 bit Excel file. The x86 install will need to be installed on the SQL Server that the SSIS package is connecting to for its source data.
Creation and Editing of the SSIS package on your workstation
The SSIS package can be built from your workstation, but will need to be flipped between the 64BitRuntime for any debugging. Open the SSIS package in SQL Server Business Intelligence Development Studio on your workstation. In Solution Explorer set the properties by right clicking on the solution name, select Debugging and set Run64BitRuntime to false.
Creating the Excel Target Data Connection String
In the Connection Manager right click and select New OLE DB Connection. Click the New button, and select from the drop down list of Providers Microsoft Office 12.0 Access Database Engine OLE DB Provider. Fill in the filepath to the xlsx file that will be used.
Click the Data Links button, and select the All tab. For the Extended Properties fill in the value as Excel 12.0. Click on all the OK’s, and now the Excel data destination is ready.
Finish Setup of the SSIS Package
Continue with setting up your source data in the Connection Manager as SQLServer as a New OLE DB Connection, using the SQL Server Native Client.
Set up Data Flow Tasks with Data Conversions as needed. I found that text data usually required a data conversion to Unicode string [DT_WSTR] to allow the data to be transferred properly.
When done creating and testing the SSIS package on your workstation, be sure to change in Solution Explorer the Debugging properties back to Run64BitRuntime to true prior to running or scheduling it to run from a 64 bit server.
The SSIS package is now ready to be scheduled in a SQL Server Agent in a job, or used in a dtexec cmd line, or however you choose to run it.
