Import/Insert Excel Range and SSIS variables into SQL table?

Question

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 this post for piping in variable data into SQL, but I haven't gotten a working model yet.

Worth noting, Excel COnnection is dynamic and set to run within a Foreach Loop container to iterate through my Excel sources. Any advice or guidance would be appreciated!

Answer 1

It sounds like you want a Derived Column task. in the task, just add the new columns you want, and map the variables to the column.