Creating Excel File using SSIS

This process creates a excel file with data from the database.

In Execute SQL Task we create the table( excel sheet) . The columns are the column names returned by the query and the names appear as it is in the header of the sheet if we have checked the box where is says first row contains header.

 The following is the data flow task where the excel destination uses a dynamic file connection manager.

 

We are creating the ExcelFilepath property of the connection manager as follows. WE declare a user variable called @varExcelFileName and build the expression for it as follows:

@varExcelFileName  =

@[User::varOutFilePath]+ Replace(@[User::varReconFileName],".xls","") + "_" + (DT_WSTR, 4) YEAR( GETDATE()  ) +(DT_WSTR, 2) MONTH( GETDATE()  )+(DT_WSTR, 2) DAY( GETDATE()  ) + ".xls"