↓
 
Home→SSIS→SSIS Export to a Named Worksheet in Excel

SSIS Export to a Named Worksheet in Excel

The Intransigent Data Blog Posted on by

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:
    • ExcelSheetName
    • SheetCreationStatement
  • 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.

Difficulties

  • 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`

Limitations

  • 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?

References

Original pattern provided by these articles

 

 

Comments are closed.

↑