SSIS, Create a Dynamic Excel file with dynamic Sheet Names

Create a Dynamic Excel File with Dynamic Sheet Names in SSIS. I found this one of the easiest ways to dynamically create an excel file with dynamic sheet names.

Step 1

SaiExcel1.png

Select SQL Task Editor and then in General -> Connection Type -> Select Excel

SaiExcel2.png
And then just below Connection Type in the “Connection” tab select “New Connection” and you will get the image to the right.

Step 3

Create a blank excel file or a dummy File to select the file for the above screen.

Need not worry about headers etc.

Step 4

Select SQL Source Type as “Direct Input”

Step 5

SaiExcel3.png
This is the Key part.

In the SQL Statement tab, write the code just the way we do for creating a table in SQL.

One can create the columns depending on the nature of column types like Varchar, INT, DECIMAL etc.

Here, in the above figure, “” refers to the Sheet name and the rest refers to the columns within the sheet.

Then Click “OK”.

If you want to have multiple sheets, then follow the above steps with multiple SQL Task Editors.

Now, all set for dynamic sheet names
SaiExcel4.png
If you want to have multiple sheets, then follow the above steps with multiple SQL Task Editors.

Now, all set for dynamic sheet names

Step 6

Now if you want to create the excel files dynamically

Select the Excel Connection Manager that is created in the Connection Managers pane and then go to Properties

Step 7

SaiExcel5.png
Click on the navigation pane of Expressions tab and you will be directed to Property Expression Editor Window.

Select “Excel File Path” from the drop down list of the Property tab in the Property Expression Window and click on “Expression” navigation pane.

Step 8

Now you will be directed to Expression builder window where you can build up your expression for the file path and file name for the excel sheet like below.

Check the expression by using “Evaluate Expression” in the bottom and when you are happy click “OK”.

That’s all you are done.

Now when you execute the package, you will see the desired sheet names with desired File name in the desired file path.

Step 9

SaiExcel6.png
Now you will be directed to Expression builder window where you can build up your expression for the file path and file name for the excel sheet like below.

Check the expression by using “Evaluate Expression” in the bottom and when you are happy click “OK”.

That’s all you are done.

Now when you execute the package, you will see the desired sheet names with desired File name in the desired file path.
Ousia Logo