如何通过SSIS的Execute SQL Task创建多工作表Excel文件？

Let's start with the basics you might already know: to create a single-sheet Excel file via SSIS with an Excel connection, you can use an Execute SQL Task:

• Configure the task to use your Excel connection manager.
• Paste this CREATE TABLE statement into the SQL Statement field:

  CREATE TABLE `EXAMPLE_SHEET` ( `COLUMN_1` NVARCHAR(200), `COLUMN_2` INTEGER )
  

• Running this will generate the Excel file—just note it'll throw an error if the file already exists, which is expected behavior.

Now, for your question: how to create an Excel file with multiple worksheets using this method?

After testing, here's the reliable solution:

• Add multiple Execute SQL Task items to your control flow (one for each worksheet you need).
• Ensure all tasks use the same Excel connection manager.
• In each task's SQL Statement, write a unique CREATE TABLE for each worksheet. For example:
  • Task 1: Creates SHEET_A

    CREATE TABLE `SHEET_A` ( `USER_ID` INTEGER, `USER_NAME` NVARCHAR(150) )
    

  • Task 2: Creates SHEET_B

    CREATE TABLE `SHEET_B` ( `TRANSACTION_DATE` DATETIME, `TOTAL` DECIMAL(12,2) )
    

  • Task 3: Creates SHEET_C

    CREATE TABLE `SHEET_C` ( `PRODUCT_CODE` NVARCHAR(20), `STOCK_QUANTITY` INTEGER )
    

• Execute these tasks (sequentially or in parallel—either works as long as they share the same connection). You'll end up with one Excel file containing all three worksheets.

Important Validation Note: #

If you try to create a worksheet that already exists in the target Excel file, the corresponding Execute SQL Task will fail with an error. This is exactly the expected behavior, as it prevents accidental overwrites of existing data.

内容的提问来源于stack exchange，提问作者Merendandum