SSIS export data to excel

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.
patd1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
patd1Connect With a Mentor Author Commented:
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
 
Molly FaganApplications Team SupervisorCommented:
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
 
patd1Author Commented:
my error.
0
All Courses

From novice to tech pro — start learning today.