Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using SSIS to export data from a 64 bit server to Excel 2007

By Krista Olson,

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.

Total article views: 5832 | Views in the last 30 days: 15
 
Related Articles
FORUM

connection from excell

sql server express 2008 connections excell

FORUM

Will DTS Excel connection function when called from SQL2008 x64?

Wanting to run DTS package under x64, with connection to Excel

FORUM

SSIS Excel Connection Manager Error

SSIS Excel Connection Manager Error

ARTICLE

SQL Server 2008,Windows 2008 x64 and Excel

Learn about how you might solve some issues running SSIS packages using Excel on a Windows 2008 x64 ...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones