Excel file at the end of the SSIS export date

When you use the ETL tool to export data documents in Excel format periodically, you sometimes require that the exported file names end in the date or time of day to be differentiated or archived.

Microsoft's SSIS is also able to meet this requirement, with a focus of two:

1. Use variable to set the Excel file name

2. Using execute SQL Task to pre-set worksheet in Excel file (similar to how to build a table)

The following is the implementation process:

Set the properties of Excel Connection Manager excelfilepath to user-defined variable.

User-defined Variable:filepath, calculated as follows:

The above steps implement the effect of the exported Excel file name ending with the date of the day, but because the file is dynamically generated and not previously established, the runtime will have problems where the data flow does not know where the field is mapped.

Therefore, you also need to create a "Table" in Excel that matches the structure and field mappings, where execute SQL Task is used. In general, this control is run in the context of database, but here it is set to Excel and ConnectionType with the CREATE TABLE statement in Excel Connection The structure of the worksheet is established in the manager's corresponding file.

The following is the result of the operation:

Excel file at the end of the SSIS export date