I have an SSIS package that is to ingest a number of Excel files with similar structures but irregular names and import them into a SQL table. Along with the data from the excel files, I have a number of variables that are set and different with each file (User::ExcelFileName, User::VarMonth, User::VarProgram, User::VarYear, etc). All of the table data from the Excel files are going to the same destination table, but for each row of data alongside the Excel dataset I want to insert a column for each variable to pass through as well into SQL. An example of my dataset is below:
Excel
| ID | Name | Foo | Bar |
|---|---|---|---|
| 111 | Bob | 88yu | 117 |
| 112 | Jim | JKL | A TU |
| 113 | George | FTD | 19900 |
SSIS Variables (set during execution)
User::ExcelFileName = c:\temp\excelfile1.xlsx
User::VarMonth = Jan
User::VarProgram = Daily
User::VarYear = 2023
Desired SQL Destination:
| ExcelFileName | VarMonth | VarProgram | VarYear | ID | Name | Foo | Bar |
|---|---|---|---|---|---|---|---|
| c:\temp\excelfile1.xlsx | Jan | Daily | 2023 | 111 | Bob | 88yu | 117 |
| c:\temp\excelfile1.xlsx | Jan | Daily | 2023 | 112 | Jim | JKL | A TU |
| c:\temp\excelfile1.xlsx | Jan | Daily | 2023 | 113 | George | FTD | 19900 |
I've tried a few configurations and I've referenced 