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.