31 Days of SSIS
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…
From Aaron…
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.
Instant Spreadsheet
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.
Excel Wrap-Up
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.
Package Download
Related posts:
- 31 Days of SSIS – Recursive SSIS Package (6/31)
- 31 Days of SSIS – UnPivot Alternative (12/31)
- 31 Days of SSIS – Raw Files Are Awesome (1/31)
