Export data to excel by using SSIS

1
votes

I am trying to export the data to excel by using SSIS packages.

I have created the data flow task and then selected excel destination as savnig the data. Then try to work on the excel connection manager and I have got the issues to export the data into Sheet 1 of the destination folder excel file. I had to create a table by using Excel Connection editor and then its loading the data to the newly created sheet in destination excel file. I don't want to have two sheets in the excel file as one is with the exported data and another one is with empty with just headers alone.

I am getting the below errors when try to export the data to Sheet 1

[Excel Destination [12]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[Excel Destination [12]] Error: An error occurred while setting up a binding for the "List" column. The binding status was "DT_NTEXT".

[Excel Destination [12]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[SSIS.Pipeline] Error: Excel Destination failed the pre-execute phase and returned error code 0xC0202025.

But it works fine perfectly when i export the data to newly created sheet "Excel Destination".

Please see the below screenshot for that.

Excel Destination editor screenshot

2
ssis

2 Answers

0
votes

Excel destination has to be set with an Excel Connection manager. The connection manager required an Excel path ( existing or not)

As soon as the Excel file exists, you can open it and delete the sheet you don't want.
So now in the excel destination name of sheet, you only have the desired sheet.

After you have selected the destination sheet, you can adjust columns in the Mappings tab

You should consider use a real excel file as destination file, in order to have all metadata up to date in ssis. Otherwise, you may have to use advanced editor.

Have a look at the Excel connections version of the file. Sometime, 2010-2017 (xlsx) metadata are out of Sync. If it can solve your issue, use excel 97 destination

0
votes

The binding type of the column should be [DT_WSTR] in the data conversion. After updated this type conversion, it's started exporting to the first sheet of the excel file.

But not sure how it was exporting with earlier when the type [DT_NTEXT] when having the two sheets in excel.