The Excel file at the end of the SSIS export date, And the excel file at the end of the ssis Export

The Excel file at the end of the SSIS export date, And the excel file at the end of the ssis Export

When you use the ETL Tool to regularly export data documents in Excel format, it is sometimes required that the exported file name end with the date or time of the day for distinguishing or archiving.

Microsoft's SSIS can also meet these requirements, with two key points:

1. Use Variable to set the Excel file name

2. Use Execute SQL Task to pre-set the WorkSheet in the Excel file (similar to the table creation method)

The implementation process is as follows:

 

 

 

Set the Excel Connection Manager attribute ExcelFilePath to the User-Defined Variable.

 

User-Defined Variable: FilePath. The formula is as follows:

 

The preceding steps show that the exported Excel file name ends with the date of the day. However, because the file is dynamically generated, it is not created in advance, therefore, the data stream does not know where the field is mapped during running.

Therefore, you also need to create a "Table" in Excel. Its structure matches the field ing. Execute SQL Task is used here. Generally, this control runs in the context of Database, but set its ConnectionType to Excel, and then use the Create table statement to Create a WorkSheet structure in the file corresponding to the Excel Connection Manager.

 

 

 

 

The running result is as follows: