November 28, 2009

SQL SERVER SSIS: How to assign Connection from variable

Last Article, SSIS - Foreach Loop Container, We need to assign dynamic connection to file connection, so SSIS For each loop Task can take each file from folder.

Lets configure File connection from variable for SSIS - Foreach Loop Container.

What we need to do is, we need to process each file from folder, so we need to assign value from variable to File connection, so SSIS Task will read that file and process that file.

To assign FileConnection dynamicaly we need to do following.

1. Right click on File Connection, click Properties.

2. Set DelayValidation = "False", as we need to assign connection dynamically.

3. Click on "Expression", and enter variable  name, which we used in SSIS - Foreach Loop Container.

SSIS Foreach Loop Container Connection

That's it.

It will assign connection from variable and process that file.

Let me know if you have any question for the same.

4 comments:

  1. How to get the every file name when looping through the For each File enumerator container ?

    ReplyDelete
  2. Hi,

    We just need to give filer *.*.
    By this way, it will select each file from folder.

    For this we need to make sure that, all files should have same format.

    Thanks,
    Tejas Shah

    ReplyDelete
  3. Hi Tejas

    How can I set value of SQLCommand property of DataReader Source dynamically while the Data Flow Task is inside the ForEach Loop Container?

    Thanks, Ujjal

    ReplyDelete
  4. Hi Tejas, great article!! But I would like to know, how would I obtain 1 file, not all the files in the directory. For example, a file such as this: 'example_yyyymmdd.txt'.

    Thank you in advance.
    Evans

    ReplyDelete