Exporting data to Excel, but to named spreadsheet(s). These notes are taken from an exercise using SSIS 2008R2.
- Create the Data Flow and export data (in required format) to an Excel Destination (creating an Excel Connection Manager in the process). Use this to extract the Sheet creation SQL.
- In Excel Destination Editor select “Table or view” as the Data access mode
- Click the New button and copy the “CREATE TABLE `Excel Destination` ( …” code. The backticks are legitimate.
- Edit the Excel Connection Manager, set DelayValidation to False.
- Create some string variables:
- In a script, generate the ExcelSheetName and assign the “CREATE TABLE…” code copied above to the SheetCreationStatement. Replace “Excel Destination” (between the backticks) with your name for the worksheet.
- Create an Execute SQL Task. Set ConnectionType = EXCEL, Connection = (your Excel Connection Manager), SQLSourceType = Variable, SourceVariable = User::SheetCreationStatement. This task will create the worksheets.
- Amend the Excel Destination
- Set Data access mode to “Table name or view name variable”
- Select User::ExcelSheetName for the Variable name.
- Excel doesn’t have a large palette of types: most columns (in the CREATE TABLE statement) are defined as Long Text, Double or Long. I found that all varchar columns had to be converted to unicode text stream (DT_NTEXT).
- Set ValidateExternalMetadata to False for the Excel Destination. (the workbook will be blank to start with).
- I got this Warning in the Create Worksheet Task – but despite this, the worksheet was created and the job ran satisfactorily
- Warning: 0x0 at Create Worksheet SQL Task: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. [warning appears twice per operation]
- During testing you’ll see this Error if a worksheet with the name you’re trying to create already exists:
- Error: 0xC002F210 at Create Worksheet SQL Task, Execute SQL Task: Executing the query “CREATE TABLE `2012-10-02`
- Unsupported characters in worksheet names are changed to _. That’s OK, but also some supported characters – such as – (hyphen) are also changed to _. A limitation of CREATE TABLE statement?
Original pattern provided by these articles