Jarrett's Tech Blog - Browsing SSIS
-
Output Large Result Sets to Excel 2007 from SSIS 2005
I was recently in a situation where I needed to output a large amount of data to an excel file. This file is generated periodically and used by business analysts. We are using SSIS 2005 and the BAs are using Office 2007. Since the report would be bigger than 65,536 row limit of 2003 format, we needed to use 2007 format.
You'll need to start your data flow with an OLE DB Source as according to this thread it is required for compatibility.
Next, in the Connection Managers, create a New OLE DB Connection. Choose Microsoft Office 12.0 Access Database Engine OLE DB Provider. For now, leave the details blank and click OK to continue.
Note: you'll need Office 2007 installed or the 2007 Office System Driver: Data Connectivity Components
You'll notice that it looks like you are using a database connection instead of the typical Excel Connection you used for Excel 2003.
Select your new connection and view the properties. Change the connection string to the value below.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
Don't forget to change the file path in the connection string.
Next, I recommend you truncate any large columns to 255 using a Derived Column transformation. Also, use a Data Conversion transformation to convert all strings to unicode strings.
Now, that your data is in the correct format, let's clean up the column names. I use a Union All transformation and delete the old columns and rename the derived and converted columns. These last couple steps make the data more compatible with Excel.
Finally, your ready to dump the data to an Excel 2007 file using an OLE DB Destination. Choose your new connection you made earlier. For the name of the table or view, choose New to create a new worksheet in the Excel 2007 workbook output. In the CREATE TABLE script, you can rename the worksheet if desired.
Because of the cleanup, you'll have a simple one to one mapping. Now you can output more than 65k rows into an Excel file from SSIS 2005.
