It’s day fourteen of the 31 Days of SSIS blog series. It’s also Friday, so I’m taking the day off. That doesn’t mean that there won’t be a post today. What it means is that I have asked Aaron Drinkwine (LinkedIn), one of my co-workers at Digineer, to write a post for today.
This post will go over a pain that plagues us all from time to time. At times we are called upon to export data into Excel. But can we do this without already having an Excel spreadsheet available? Read on and find out…
Trouble In Excel Land
We all have to face it, spreadsheets are never going away. Business users love to see things in a spreadsheet. They almost thrive on being able to slice and dice it in any way they know how. With that, we’ve probably all had to deal with exporting to Excel but have found it to be…unpleasant. This seems to be especially true when we try run these projects but have found we must have a template Excel file somewhere in order for SSIS to map to before it will populate it, even in debug mode. Well, it turns out there’s a way for us SQL lovers to do it using some SQL logic and alleviate some headaches!
The problem with having a template is that it has to be in its expected location before SSIS will do its thing. If, for whatever reason, that file isn’t there you may get a call at 3 AM for a job that failed as a result of the network backup process being interrupted by a bad NIC card, or because the Dread Pirate Roberts came and deleted the template. Whatever the reason, it’s no fun getting that call when you should be able to ensure your package can run with as few dependencies as possible.
Enter ’SQL logic’ stage left. By simply adding a couple of Execute SQL Task (EST) steps to your control flow, you can ensure that the Excel file exists for your Data Flow Task (DFT) to map to. You could even set it up to dump to a backup directory in the event the bad NIC card made your designated Reports network directory not available (but that’s outside the scope of this post).
The first thing you’ll need to do is to make sure the “Run64BitRuntime” property, under the project properties, is set to FALSE. Next, create a Data Source connection to the database of your choice. Now create an Excel Connection to a destination of your choice with the expected worksheet name and all the column names for what you’ll be exporting – this is just for the initial setup of the project, later on we’ll establish the final destination and not need this file.
We’re now ready to drop an EST onto your Control Flow tab. Using the editor, set the “Connection Type” = Excel and leave the “ResultSet” property = “None”. For the “SQL Statement”, put in the same CREATE TABLE logic you would when creating a table in SQL – keep in mind Excel will need everything in Unicode and most SQL data types will generate errors. This CREATE TABLE statement will need to be for all the columns you’re exporting:
CREATE TABLE `MyWorksheetName` ( `Name` LongText, `ID` LongText, `XType` LongText )
Next we can create our DFT to use for exporting. In this step add your SQL source connection and do whatever manipulation of the data you’ll need. For the destination, drop an “Excel Destination” down and set it up to map to the previously set up Excel connection (from the initial setup). Map to the worksheet name (a.k.a. table name) and map the columns. That’s it, now you’re ready to export!
In the event the spreadsheet already exists in your final destination folder during runtime, you will either want to delete it prior to creating it or just overwrite the data in it. To do the former, add a For Each File loop to your control flow looking in the directory for the expected spreadsheet name (including filename). In this you loop, add an EST with a “DROP TABLE MyWorksheetName” statement. Then have the CREATE TABLE step as the next step outside the loop.
By implementing this solution, you could save yourself, and the person who inherits the project, a fair amount of headaches by ensuring SSIS can see the Excel it’s expecting.
Using Excel with SSIS can be a pain for many reasons. Hopefully, Aaron’s SSIS package is as helpful to you as it’s been to those of use that have used this in the past. In the future, this pain no longer has to cause you frustrations.
14 thoughts on “31 Days of SSIS – Instant Excel Spreadsheet (14/31)”
How to delete the sheet bro?
unable to open the excel, throwing incorrect file format error
Thanks for stopping by. What version of Excel are you using?
Thank you for writing this post. Here is what I found:
The key to export successfully to an Excel file from OLE DB source is the datatype conversion, as demonstrated here as CAST function. The Execute SQL Task is basically useless. All you need is a DFT which has a OLE DB source connected with an Excel destination. When you configure destination, make sure to click on the NEW button and create a table(worksheet) before click on the Excel sheet drop down menu, where you can find the newly created worksheet.
If you hate CASTing every column, use a Data Conversion Task between the source and destination tasks.
Hi Larry, that sounds like it’ll work as well. I’ve had issues with that in previous versions, but not sure the exact error messages any longer. The one benefit I see with the Execute SQL task is being able to add it to an existing workbook and define the name at run-time, though the example above doesn’t highlight this.
Could you explain how to do this. We were requested for a one time information – data consisting of >65K rows. I wanted the remaining rows to flow over to the next sheet and so on. However, as I could not achieve that, and it was one-time, I ended up breaking the data into chunks (based on date) and running it multiple times into different excel sheets. 😦 Let me know if there was a better way
One way would be to use a script component for a data destination and then rotate the tab in the workbook every 65K rows. This might now be the best route, though since it would require a lot of .NET code to get it working. Another option would be to stage the data with a row_number() value and then mod that by 65K to determine the tab to place the data on. Then put the data flow in a foreach loop to hit each tab in the workbook.
Great information (as always) and I like the fact that you have guest bloggers.
Comments are closed.