I have an SSIS job that loads Excel files from a UNC path into a staging SQL table, then executes a stored procedure at the SQL server to load the data into a production SQL table.
What I need to do is to ensure that along with the data from Excel, I am also loading a SSIS variable.
Say that the Excel has 10 columns. My target SQL table has 11 columns. The 11th column is for the name of the Excel file that was loaded, which I need to load alongside the Excel data.
This is an oversimplified example but it is pretty much it.