Load data in pre-formatted excel file in SSIS

Suppose we have requirement like we need to create a daily excel report. In this report having its own format and after that we need to load the data. For loading data in excel is easy but when we trying to load data in formatted excel is bit difficult. We need to set some properties.

This is the pre- formatted excel. We need to load the data on daily basics.

This is my package design.

Let’s learn how we will do it.

Open the SSDT.

Take the File system task.

I have created a master pre-format excel sheet. Whenever we running the page first I need to copy the master excel file to destination folder and then we need to load that data in destination folder excel file.

Taking the file system task

I am creating some variables

Like SourceTemplateFile, DestinationFolder and FileFullName

Value of the SourceTemplateFile is the location of the master formatted excel sheet.

 

Now setting the value of this variable in package.

Similar way setting the value of DestinationFolder

See here I am creating dynamic excel file according to the date so I am creating dynamic name of the excel file using expression

No I am configuring the file system task source and destination values.

1.       Destination Connection

Here I am using destination path is as variable so I select ISDestinationpathVariable is True. Assigning the destination variable and keep Overwritedestination is true.

2.       Operation

Here I am performing Copy operation

3.       Source Connection

Using source as variable and assign the value of the source variable.

Now Copy operation is completed successfully. Running the package and checking the output.

Package executed successfully.

   

Now I am taking data flow task.

Double click on the Data flow task

In data flow task I am taking the Source as OLEDB

Creating the connection

Here I am using AdventureWorksDW2008R2 database.

select p.EnglishProductName as [Product name],pc.EnglishProductCategoryName as [Product category name], psc.EnglishProductSubcategoryName as [Product sub category name], sum(f.SalesAmount) as [Total sales]from DimProductCategory pc inner JOIN DimProductSubcategory Psc ON PC.ProductCategoryKey=psc.ProductCategoryKey inner join DimProduct p ON p.ProductSubcategoryKey=pc.ProductCategoryKey inner join FactInternetSales f ON f.ProductKey=p.ProductKey group by p.EnglishProductName,pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName

 

 

Map the columns.

Click ok.

Now I am taking the destination as excel.

Now I am creating the connection.

First we mapping the columns of excel. Keep in mind we are creating the excel file.
Remember unchecked the First row column name. 

 

Map the column.

Make the excel connection dynamic using expression.

 

Click Expression and the properties you select Excel path.

Click ok.

Now the most important properties we need to set on Data flow task on DelayValidation is true.

 

And also we need to set this property on Control flow on DelayValidation is true.

 

Now the package is ready to run.

Now I am running this package.

 

Package executed successfully.

See the output.

 

Hope this is helpful for you.

Thanks!!!