Excel values to be assigned as variables in SSIS

Asked By jessy_neil 0 points N/A Posted on - 06/27/2011

Hi fellows,

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.

Variable          Value
File_name        file_a
File_path         “c:aa.txt”

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?

Thank you.

• Status: Open
• Question Views: 1780
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By ryan_crewsh 0 points N/A #129184

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.

About ryan_crewsh

Questions
1

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/27/2011
• Question Category: MS SQL Server