Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS export data to excel

Posted on 2011-09-26
3
Medium Priority
?
627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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