SSIS - SQL Task, ADO Connection, and Excel xlsx file issues

1
votes

So I have a relatively simple SSIS job that does the following:

Execute SQL Task - Drop Sheet from Excel File

drop table `Table`

Execute SQL Task - Create Sheet in Excel File

CREATE TABLE `Table` (
`Col1` VarChar (255) , 
`Col2` Long , 
`Col3` VarChar (84) , 
`Col4` VarChar (60) , 
`Col5` VarChar (255) , 
`Col6` VarChar (20) , 
`Col7` VarChar (255) , 
`Col8` VarChar (255) ,
`Col9` VarChar(255))

Data Flow Task - Export Data from SQL to Excel

This just runs an SQL query [OLE DB Source], coverts everything to unicode strings, and exports the data to an Excel Destination.

NOTE: This job executes perfectly with no errors in BIDS 2005. However, when I initially tried running it in BIDS 2008 (32 bit mode), I got the following error on both the Drop Sheet and Create Sheet Execute SQL Tasks mentioned above:

Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I found I could fix this by changing the ConnectionType property of my Execute SQL Tasks to ADO, and using the following connection string:

Data Source=\\<filelocation>\<ExcelFileName>.xls;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;

At this point, I'm back to the package executing in BIDS 2008 with no errors. Everything runs great!

Then I tried to update the job from exporting to an Excel 97-2003 .xls file to exporting to an Excel 2007 .xlsx file.

So, per Microsoft, I had to change my Execute SQL Tasks to use the following connection string:

Data Source=\\<filelocation>\<ExcelFileName>.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 XML;

I also had to update the connection manager for my Excel Destination step (which DOES support the Excel 2007 format in BIDS 2008 per Microsoft) to the following:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\<file location>\<Excel filename.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

So at this point, all the connections test good, and everything APPEARS like it should work. However, when I execute the package I have the following problem.

  1. The Drop Sheet Execute SQL Task completes successfully according to BIDS. However, it doesn't actually drop the sheet in the Excel file anymore. It DOES delete all the data contained in the sheet though.
  2. Because of #1, the Create Sheet Execute SQL Task fails because the sheet was never actually dropped.

Any ideas why this isn't working anymore? Honestly I've looked all over the internet and SO, and I have yet to see someone explain how to do this. Is there some new command to drop a sheet in Excel 2007?

1
sqlexcelssisexcel-2007
As far as I know this is not supported in Excel 2007. There are some workarounds in this link that might be useful. – Frank Goortani
Hi Frank, thanks for the response! Out of curiosity, the only workaround I see at the link involves running some code in the job that requires you to have MS Office installed on a production SQL server (Will never fly at my work). I see they note something about using a template file and copying it? I'm not super clear on that, do you have any examples? Or can you clarify? Thanks again!!! :) – Oryx

1 Answers

1
votes

For anyone else who may find this:

I fixed the issue with exporting data to XLSX files by replacing the two Execute SQL tasks with two File System Tasks. One that deletes the existing file, and another that copies a "template" Excel file (basically just an empty spreadsheet with column headers) to the reporting directory.

Then I export the data to the new template file using the data flow task.

Not my ideal solution, I much prefer the old method of having one file and just re-creating the table. However, apparently that's no longer an option in Excel 2007, and the File System task method using a template file will work.

The two most helpful resources I found when working on this job are:

SSIS - Excel 2007 Connection Guide

Copy/Rename Files Using SSIS File System Task