SSIS Excel to SQL Server - Need to add SSIS variables into the mix

Question

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.

Answer 1

Does the Excel Connection Manager use a variable for the file name (ExcelFilePath property)? If so, try adding a derived column with this variable as the value. Otherwise you can add a Script Component within the Data Flow Task as follows to create a new column that will hold the file path.

• Add a Script Component. On the Input and Outputs pane, expand the Output 0 folder, select the Output Columns folder and add a new column. Name this column as you want the column holding the file name to be named and pick the proper data type.
• On the Connection Managers pane of the Script Component, add the Excel Connection Manager that is being used and provide an appropriate name for the Name property.

• In the script, the following code will obtain the file name and set the new column to this value from the IDTSConnectionManager100 object. In this example, ExcelConnection is the name that was given to the Excel Connection Manager on the Script Component editor and ExcelFilePath is the name of the new column that was added in the Data Flow. From this point, the new column can be mapped to the corresponding column in the destination table.

  IDTSConnectionManager100 ConnMnger;
  ConnMnger = this.Connections.ExcelConnection;
  
  Row.ExcelFilePath = ConnMnger.ConnectionString;