8/03/2018

Export Data to multiple excel sheet from SQL server Table using SSIS


Export Data to multiple excel sheet from SQL server Table using SSIS
Below solution where we need to export SQL data into multiple excel sheets, as we have below data in our SQL Table:

LoanID       State         TYPE        STEP
2                   CA            R6            1
3                   CA            R6           3
4                   CA            P1            4
67                 CA            P1            5
45                 CA           R2            7
23                 CA            R2            8
Now we want excel file (Destination) to have multiple tabs using column 'State' and 'Type'.
Below is how result in excel should be:
LoanID       State         TYPE        STEP
2                   CA            R6            1
3                   CA            R6           3

Second tab should be 'CA-P1'
LoanID       State         TYPE        STEP
4                   CA            P1            4
67                 CA            P1            5

And Third tab will be 'CA-R2'
LoanID       State         TYPE        STEP
45                 CA           R2            7
23                 CA            R2            8
Solution:
First tab should be 'CA-R6'