N/APosted on - 06/27/2011
I have an excel file which consist of file name and location path. I want to take those two values for variables in SSIS. For example, in the excel sheet, in the first row, I have entered “file_a” in the first cell and the “c:aa.txt” the file location path. I have created two variables “file_name” and “file_path”. I want those two values in the excel to be assigned to these two variables.
So I can use these variables in other tasks. But the input must be from the excel sheet. Can anyone help me to do this task?
Excel values to be assigned as variables in SSIS
In this case, you can use the For Each Loop container to retrieve all the data from the excel sheet and assign those values to the variables in the SSIS.
Select the For Each Loop Container and double click on it to navigate the Editor page. Then select the type as Item Enumerator. In the Connection Manager, create an Excel Connection manager and configure it to the excel sheet. In the input option, enter as “Select * from [$Sheet_name]” which holds the entire sheet.
Create a variable and set as object. In the For Each Loop Container, select the object and go to "Variable Mapping" tab. There enter the two variables and put the index as 0 and 1.
Now, the variable is mapped to those values in the excel sheet. If you want to test it, place a Script Task and display the variables’ values in a message box.
This could resolve your issue.