Solved

SSIS export data to excel

Posted on 2011-09-26
3
587 Views
Last Modified: 2013-11-10
Objective: To export data in an excel sheet using sql.

I created an OLEDB Connection manager named 'ProdDBConnection'. I created a data flow task on the control flow tab. Then On the data flow tab I created an OLEDB Source with Data access mode as sql command. It has ProdDBConnection as its OLEDB connection manager and data access mode as SQL command.. Succesfully tested the sql using 'Parse Query' button and also 'Preview' button

Now, I connected the OLEDB Source to an Excel Destination task. Then I created an Excel connection manager that connects to an xls file on the local drive. Now I am trying to configure the Excel Destination. It is asking for an OLEDB Commection mamager, but does not accept ProdDBConnection as the connection mamager name. It also has Data access mode as SQL command, but when I try to parse the same query that I provided above to OLEDB Source, it throws an error: "SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error has occurred. Error code: 0x80004005. An OLE Db record is available. Source: "Microsoft Jet Database Engine" Hresult: 0x80004005 Description: Could not find installable ISAM."

Please help in setting up my Excel destination.

Thanks.
0
Comment
Question by:patd1
  • 2
3 Comments
 
LVL 6

Expert Comment

by:mjfagan
ID: 36601996
Check to make sure you have your package set to run in 32 bit--with Excel in SSIS, you have to run it in 32 bit.  To do this, click on Project, Properties, and in the Debugging area, look for the Debug Options and you'll see the Run64BitRuntime--change it to false.
0
 

Accepted Solution

by:
patd1 earned 0 total points
ID: 36602027
I figured out the problem. For the excel destination destination I should choose OLEDEB connection manager as my excel file connection manager, data access mode  to 'table or view' and then select sheet as 'sheet1'. That solved my problem.
0
 

Author Closing Comment

by:patd1
ID: 36895903
my error.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now