0

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.

  • Worth saying that if I just want to add a column after the initial load, it is easy. The issue is if the column is a NOT NULL column so the variable value must be loaded alongside Excel. – pmdci Nov 23 '18 at 15:23
0

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;
    
  • Ah! Derived column! Thanks for that. Yes it uses a variable and I was just thinking which SSIS Toolbox element I would have to explore. I'll ave a look into it. – pmdci Nov 27 '18 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.