Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Dynamic file connection in SSIS using for each loop

Nov 18 2009 10:28PM by Sudeep Raj   

How to set the file name dynamically for multiple files kept in a folder or different folder.
Point to note that all the files have the same metadata.<
For simplicity sake I will be using a case where the file names and file paths are saved in a DB table. There is an ID auto increment field to based on which the files need to be picked and inserted into the destination Table.
The above table’s Schema is:
 CREATE TABLE [dbo].[FileConfig](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FilePath] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The input file data is comma delimited
The destination is SQL Table.
The data will be modified as per the definition given below.

Input  File Columns
Output  File Columns
ID -> String
ID -> unsigned Integer
FName -> String(50)
FName -> String(50)
LName -> String(50)
LName -> String(50)
Age -> String
Age -> unsigned Integer
DOB > String(DD/MM/YYYY)
DOB > DateTime

So the Package will look like this:

Create 2 string Variable: FileName and FilePath with the scope as the package(Practice in my case). Set the value of the variable to a file name and path that exists (I set it to “File1.txt” & “C:\SSIS\Practice\Files”) just while development.
Next create an object variable(ObjfileDetails) to store the result set from the initial Execute SQL Task.

Now set up the Execute SQL Task to get the file name and path. We save the full result set to an object variable.
ResultSet: Full Result Set
ConnectionType: OLEDB(that’s what I have used you could also use ADO.NET)
Connection: Select the appropriate one.
Note: it is ordered by ID column.

Set up the Result set as shown in figure

Note: The Result Name is set to 0.
Once the SQL Task will be executed this object variable will containing the entire result set returned by the query.
In the collections tab do the below settings:
Use the object variable in the For each loop.
Set the enumerator to Foreach ADO Enumerator.

Next goto the Variable mapping and do the below mentioned settings:
Select the 2 string variable to which the file name and file path will be saved for each iteration of the object variable.

Note: Set the index starting from 0 and the order should be same as the order of the columns fetched in the SELECT query above.

Once this is done we put a Data Flow Task(DFT) in the above For Each Loop.
In the DFT use:
1.Flat File Source to fetch the data from the input file.
2.Derived Column transformation to change the string date field to DB_DBDATE
3.OLEDB Destination to send the valid records to database table.
4.Flat File destination to log the error records.
Let’s have a look at the layout of the DFT.

In the Flat file source we make a new connection manager where we set browse and set the file path of an existing file.
In the Advanced tab set the Column Names as per the need and set the data type to which you can convert the data without the loss of information like the numeric columns. Leave the other to default or set the max width of the string columns.

Once the Flat File connection manager is set up select the columns you need in the flow and your done.
Next connect the Flat File Source to the Derived column to change the DOB column to DT_DBDATE format.
To do this in the derived column add a new column give it a name and set the expression to :
Once the expression is set click the Configure Error Output button at the bottom.

In case of error in the Derived Column transform (e.g. the date field is No Value, “00/00/000” or any invalid date) set it to redirect row so that it can be logged and analyzed.

For the correct records send the data to Table
In the OLEDB Destination:
Select the OLEDB Connection needed to connect to the destination Data base.
Select the table where the columns need to be inserted.

Goto the mapping
Go the mapping and do the mapping as required and click OK.

For the error records(red arrow) after the derived Column set a flat file destination to log the error records.
The DFT is now all set.
Now the DFT Flat File source needs to be made dynamic.
To achieve this follow the steps below.
Goto the connection manager right click-> Properties

In the properties pane for the above connection manager:
Set the DelayValidation to True
Expand the Expressions by clicking the ellipse.

On the Property Expressions Editor window:
In the Property column select the ConnectionString property from the drop down list.
Now expand the Expression column by clicking the ellipse. An Expression Builder window will pop up.

On the Expression Builder window:
Set the Expression as:
 @[User::FilePath]+ “\\” + @[User::FileName]
Click the Evaluate expression button at the left hand bottom to see if the expression is creating the desired output.
Click OK.
Click OK on the Property Expression Editor as well.

Save and build the package.
Now when you execute the package the package will take the filenames from the DB table and dump the data in order in the final table.

You can download this package from here. 
Let me know if you need any further clarification.

Tags: SSIS, Expressions, Execute SQL Task, For EAch loop, MSBI,

Sudeep Raj
12 · 13% · 4306



  • nice work bro....

    commented on Jan 6 2011 9:29AM
    2095 · 0% · 8
  • Hiiii,, well i tried doing this....but my problem is dat i want to import all files from a folder dynamically into respective tables in a database(for eg: InterestSchedules.txt into StgInterestSchedules ie. into a staging database and ISDate.txt into StgISDate so on n so forth...) which i m not able to achieve... n after importing into staging database i will have to import this into ODS database in a similar fashion into respective tables but along with validation one with errors have to be logged into another file. After all this is done i vil hv 2 schedule a package as one job.So that if some files is added later on it will automatically move into detination table. Kindly suggest me some ideas thru which i can achieve the same.

    Regards, Saumya Shetty

    commented on Jun 6 2011 2:19AM
    Saumya Shetty
    710 · 0% · 47
  • Saumya, For this kind of tasks from SSIS you have 2 options:

    • Schedule the package to run every minute(or any interval) the first task in the package checks for file presence, If not there the packge completes there else proveecs and ETL happens.

    • Use the file watcher task to continuously monitor the folder. Onc the file is dropped the package should execute and process the files.

    commented on Jun 13 2011 3:47AM
    Sudeep Raj
    12 · 13% · 4306
  • Sudeep...very well explained thanks for the article....

    commented on Sep 18 2011 7:07PM
    294 · 0% · 148
  • Hi Sudeep,

    Your post are useful forever, please keep it alive. Most of Images not available. Please repost if possible.

    commented on Jan 30 2013 6:22PM
    2916 · 0% · 3
  • Thanks Rupesh for your feedback. I will take a look at the images and repost them

    commented on Feb 1 2013 11:05AM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Rupesh, Could you tell me which images are you not able to view? as I am able to view all of them from my home and office.

    commented on Feb 6 2013 2:15PM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep, You are right, I am able to view from my home too. Might be possible that due to polity images get blocked in my office, but i can view all images on your other posts from the same system.

    Any way thanks for everything and keep posting.

    commented on Feb 6 2013 4:39PM
    2916 · 0% · 3
  • This comment is waiting for moderation.

    commented on Mar 13 2015 2:29AM
    3162 · 0% · 2

Your Comment

Sign Up or Login to post a comment.

"SSIS - Dynamic file connection in SSIS using for each loop" rated 5 out of 5 by 1 readers
SSIS - Dynamic file connection in SSIS using for each loop , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]