Tuesday 2 July 2013

SSIS - Capture Filenames while looping through multiple files inside folder

 SSIS - Capture Filenames while looping through multiple files inside folder

In this post,I am going to show an example on how to capture filenames while looping through multiple files inside folder.

In this example,I am going to capture filenames of files located in a folder.
This will be useful to keep a track on list of files already got processed.





Step 1: ForEachLoop Container with DataFlow Task




   Create variable inside ForEach Loop Container to hold filenames while looping through files inside folder.   







 Step 2:
Goto the property of the variable ,set EvaluateAsExpression = True







Step 3: Drag-drop FlatFile Source & OLE DB destination

  Create  ConnectionString for FlatFile Source - > pointing to files in folder,
               ConnectionString for OLE DB destination -> table to hold filesnames
  and then map the columns of source & destination







    



To Capture FileNames 

 Right-click on FlatFileSource ->Show Advanced Editor... ->Component Properties ->Custom Properties -> FileNameColumnName ->give some non empty string to capture filenames















Right-Click on FlatFile Source - > Properties - > DelayValidation - > True
& in Expressions - > ConnectionString - > map to the variable created inside ForEachLoop Container




Result:











No comments:

Post a Comment