SSIS Excel output from SP with a Temp table

ojmp2001 ojmp2001 121 Reputation points
2021-10-04T16:30:37.543+00:00

I have an SSIS pkg that goes through the following steps:

  1. Pull data from Server A And Server B
  2. Put the data in Server C
  3. Server C has a Stored procedure that combines and compares the data from Server A and Server B
  4. Output in server C from step 3 is in a temp table That I would like to put in an excel Sheet and email it to users.

How do I do step 4?
I am using VS2017. There are seral columns in the output

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,454 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-10-05T04:01:24.857+00:00

    Hi @ojmp2001 ojmp2001 ,

    Thanks for your posting.

    1. Install Microsoft Access Database Engine to make sure you could connect to excel successfully. The connectivity components for Excel are not installed by default.
    2. Control Flow task 1 - Data Flow:
      OLEDB Source. Choose the source table/view/use a query...
      Excel destination.
    3. Control Flow task 2 - Send Mail Task. Attach the above generated Excel.

    For more information, please refer to below links:
    Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)
    Send Mail Task
    MSBI - SSIS - Email Excel File Report - Part-129

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.