OBJECTIVE : Creation of Multiple Excel Sheets in a Single Excel File
SUMMARY : An Excel file "MyExcel" should be dynamically created. This Excel File "MyExcel" should have multiple Excel Sheets like "MyExcel1", "MyExcel2", "MyExcel3" dynamically created in it. The same data from the source table should be sent to all the Excel Sheets.
Coding Information :
1 - Variable Creation :
a) StartCount : Specifies starting count for the Excelsheets,
Default value = 1
b) EndCount : Specifies the ending count for the Excelsheets,
Default value = 3
c) FilePath : Specifies the Path where the Excel File is present,
Default value = L:\practice\ssis\ExcelSheets\testexcel\
d) FileName : Specifies the Name of the Excel File,
Default value = MyExcel
e) QueryExcelSheetCreation : Contains the query to create the excel sheets dynamically.
It has "Evaluate As Expression" property set to TRUE.
Default value = "CREATE TABLE `"+@[User::FileName] +
(DT_WSTR, 25) @[User::StartCount]
+ "`
(
`Year` Decimal(5,0),
`Month` LongText,
`Balance` Decimal(10,0)
)"
Example = CREATE TABLE `MyExcel1`
(
`Year` Decimal(5,0),
`Month` LongText,
`Balance` Decimal(10,0)
)
2- Connection Managers :
a) Excel connection Manager
:: @[User::FilePath] + @[User::FileName] + ".xls"
:: Set the "ExcelFilePath" property of the Excel
connection manager to above expression
b) DataBase Connection Manager
c) File Connection Manager
* The Excel file present in the FileName variable has to be physically present initially so that in the DFT the mapping between source columns can be done with the destination columns. Else the Excel Destination will give error during the creation of the package.
3- Components Description :
The ForLoop "iterate for multiple sheets" iterates for EndCount times. If the value of EndCount is 5 then the for loop iterate 5 times and creates 5 Excel Sheets in the same Excel File.
The Execute Sql task "Create Excel sheet in existing Excel File" creates the Excel Sheets in the Excel File during run-time.
* The ConnectionType = EXCEL which means the object operated upon is an Excel object
* The command/query to create the excel sheets are passed through variable "QueryExcelSheetCreation"
The value in this variable keeps changing with the iteration of the For loop. So every time the Execute Sql Task is executed a new Excel Sheet is created in the corresponding Excel File. For example MyExcel1, MyExcel2, MyExcel3,...........
The variable "QueryExcelSheetCreation" has Evaluate As Expression property set to TRUE which enables its value to change with the iteration of For Loop.
** Note :
The Excel Connection Manager is used to create an Excel File with the specified name. (here MyExcel)
The Execute Sql Task configured using these Excel Connection Manager creates the specific Excel Sheets in the same Excel File.
(here MyExcel1, MyExcel2, MyExcel3)
At Run-Time :
- "Delete Preexisting File" File System Task deletes any existing Excel file with same name "MyExcel"
- "Iterate For Multiple Sheets" For Loop iterates for n number of times where n = (EndCount-StartCount)+1
- "Create Excel sheet in existing Excel File" Execute Sql task creates an Excel Sheet after creating the Excel File to which the "Excel connection Manager" is pointing to.
The Excel File is created with name depending upon the value of the variable FileName.
- "Transfer records To new Sheet" Data Flow Task transfers the source records to the dynamically generated Excel sheets.
Coding Information :
1 - Variable Creation :
a) StartCount : Specifies starting count for the Excelsheets,
Default value = 1
b) EndCount : Specifies the ending count for the Excelsheets,
Default value = 3
c) FilePath : Specifies the Path where the Excel File is present,
Default value = L:\practice\ssis\ExcelSheets\testexcel\
d) FileName : Specifies the Name of the Excel File,
Default value = MyExcel
e) QueryExcelSheetCreation : Contains the query to create the excel sheets dynamically.
It has "Evaluate As Expression" property set to TRUE.
Default value = "CREATE TABLE `"+@[User::FileName] +
(DT_WSTR, 25) @[User::StartCount]
+ "`
(
`Year` Decimal(5,0),
`Month` LongText,
`Balance` Decimal(10,0)
)"
Example = CREATE TABLE `MyExcel1`
(
`Year` Decimal(5,0),
`Month` LongText,
`Balance` Decimal(10,0)
)
2- Connection Managers :
a) Excel connection Manager
:: @[User::FilePath] + @[User::FileName] + ".xls"
:: Set the "ExcelFilePath" property of the Excel
connection manager to above expression
b) DataBase Connection Manager
c) File Connection Manager
* The Excel file present in the FileName variable has to be physically present initially so that in the DFT the mapping between source columns can be done with the destination columns. Else the Excel Destination will give error during the creation of the package.
3- Components Description :
The File System Task "Delete Pre-Existing File" deletes the Excel file (here MyExcel) if it is existing in the specified location.
The Execute Sql task "Create Excel sheet in existing Excel File" creates the Excel Sheets in the Excel File during run-time.
* The command/query to create the excel sheets are passed through variable "QueryExcelSheetCreation"
The value in this variable keeps changing with the iteration of the For loop. So every time the Execute Sql Task is executed a new Excel Sheet is created in the corresponding Excel File. For example MyExcel1, MyExcel2, MyExcel3,...........
The variable "QueryExcelSheetCreation" has Evaluate As Expression property set to TRUE which enables its value to change with the iteration of For Loop.
** Note :
The Excel Connection Manager is used to create an Excel File with the specified name. (here MyExcel)
The Execute Sql Task configured using these Excel Connection Manager creates the specific Excel Sheets in the same Excel File.
(here MyExcel1, MyExcel2, MyExcel3)
At Run-Time :
- "Delete Preexisting File" File System Task deletes any existing Excel file with same name "MyExcel"
- "Iterate For Multiple Sheets" For Loop iterates for n number of times where n = (EndCount-StartCount)+1
- "Create Excel sheet in existing Excel File" Execute Sql task creates an Excel Sheet after creating the Excel File to which the "Excel connection Manager" is pointing to.
The Excel File is created with name depending upon the value of the variable FileName.
- "Transfer records To new Sheet" Data Flow Task transfers the source records to the dynamically generated Excel sheets.