Update Query in For each loop container

Question

"How to write sql task and vary something based on a variable in foreach loop container"

I have created a table(Dummy) with 10 columns. These columns will exactly replicate the input files. In a folder i have five excel files with different names. Each file contain say 100 line items. While loading all these files in my table, i need to capture each of the filenames.

So, i created a column in my table(Dummy) as "File_name". I created foreachloop in SSIS, and declared the variable by referencing the folder and path name. How do I write an update query in SQL task to pick this variable and set as file_name for all the rows loaded?

Answer 1

What's your WHERE Clause in Update Statement(Is there a column in Dummy table that Identifies each set of data from different files),unless you have column or set of columns that identifies each set of data Update does not make sense here.

Other alternative is to have your INSERT(Data flow task) and Update SQL task(Where file_name IS NULL or empty) in same For each loop container and parameterize your Excel file connection as well;

Here is the link to Pass variable to Excel Connection : SSIS with variable excel connection manager