Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

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.
0
patd1
Asked:
patd1
  • 2
1 Solution
 
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:
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
 
patd1Author Commented:
my error.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now