create excel file dynamically using ssis

This post is a continuation of a previous blog and if you are new to ETL process using SSIS check it first to get more tips on how to solve most of the errors you might solve while working on this one.
We will begin where we left off the previous blog and the requirement that we have to solve is we need to create excel file dynamically using ssis with some file name plus and the year month day to indicate when the report is created or the ETL executed. For Example “SalesPerson2013-10-13.xlsx”
It is a good idea to always name your tasks and connections something that is meaningful so that you when you start to work hands of those task in a single project. Let us begin by renaming our source OLE DB Source to “SRC AdventureWorks” and our destination toe “DST Excel”

Now let us create a variable to make our life easier. Change the variable Name to FileName in the variable property change EvaluateAsExpression=True and enter the Expression for your folder\filename.xlsx

For this exercise give the folder name the file name and year month and day as follow. Don’t forget to escape all the illegal characters in the file name by using backslash “\”
For our case the expression looks like this one.

“D:\\ssis create excel file\\SalesPerson” + (DT_WSTR,4) YEAR( GETDATE()  ) +”-“+  (DT_WSTR,2) MONTH( GETDATE()  ) +”-“+  (DT_WSTR,2) DAY( GETDATE()  ) + “.xlsx”

Now Add a File System Task to your work area and drag the green precedence constraint to Data Flow Task.

What we are going to do here is since we just can’t create an Excel file out of a blue we will be use our previous Excel File as a template then copy other files that looks like that and change their name to the expression we used. In the template file you may remove any existing data and leave the worksheet names and column headers unchanged.
Now double click on the File System Task and change the properties like this one.

Now click on SourceConnection and create connection to the previous template Excel file we created before.

Now let us create a connection to the Excel file that is yet to be created being in our Data Flow Task.
Right click on Excel Connection Manger and select Properties

Next in the Excel Connection Manager all we care about is the ConnectionString that it has to be dynamic to support the new files that we are going to create each month. To do that first click on Expressions then on the next dialog select connection string on the left then click on “…” you will have another dialog open there type your connection string as
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” +  @[User::FileName] + “;Extended Properties=\”Excel 12.0 XML;HDR=YES\”;”
And test your expression by clicking on “Evaluate Expression”

Since our file is not going to be create until runtime we need to change the Data Flow Task properties DelayValidation=True

Finally execute your package

And our file created same way as we expected with a filename and date of the report generated.