Inserting .DBF Files from different folder structure with their respective FilePath using SSIS?

Question

Am working on creating SSIS Package the does the following, so far my SSIS Package works well to insert records from '.DBF' file from one folder location ONLY (Am using Data Flow Task onto the Control Flow and OLE DB Source/Destination to read a .DBF file and get it inserted). However, am not able to figure out how to make it works with 20 sites, for example: I have 20 '.DBF' files stored in the following structure:

1. E:\DBF Files\Site1\Data\records.DBF
2. E:\DBF Files\Site2\Data\records.DBF
3. E:\DBF Files\Site3\Data\records.DBF
4. E:\DBF Files\Site4\Data\records.DBF

and so on till Site20 (here file name remain the same but the content will differ from site to another).

I have table for 'records.dbf' file in my SQL Server in the following structure:

Records Table:

Product_Name          Price          FilePath          FileName
---------------------------------------------------------------

now here, how can process these files from different sites to be stored in my records sql table with it respective FilePath using SSIS??

P.S File Names will always remain the same and the path will change "Like Site 1"

Now that I'm looking at your question again, I'm really not sure what you're asking. What are Column 1 & 2? Do the file paths/names ever change, or always stay the same? — Tab Alleman
@TabAlleman Here the file names will always remain the same, only the file path will change and here the changing variable is "Site1, Site2..etc" and for the columns that what the file includes "the attributes basically" — Omran Moh'd
Dave is on the right track. I don't have a snapshot handy, but try googling to learn about the ForEachLoop container in SSIS, and how to use an Expression in a flat file datasource in SSIS. That should tell you all you need to know. — Tab Alleman

Dave Sexton Dave Sexton · Accepted Answer · 2016-01-03T00:23:07

Grab a For Each Loop container for the SSIS Toolbox and drag it on to the Control Flow designer.

Double click the For Each container to bring up the editor, click the Collection tab from the left-hand menu

Leave the Enumerator set at Foreach Item Enumerator and click the Columns button in the lower right.

Click the Add button and just go with the defaults which should be Column 0 and String. Click OK to return to the previous dialog. In the new Column 0 column add your folder paths one per row.

Next click the Variable Mappings tab from the left-hand menu, for the drop-down menu in the Variable column select New Variable. Again go with the defaults except for the name which in my example I have called dbf_path.

Next click OK to complete the For Each. Now the difficult bit for me because I don't what tasks you currently have that are working for one file. Whatever it is you have working, select it with the mouse and then drag and drop it into the For Each container.

For each task or tasks that need to use the file path do the following: double click it to edit, in the left-hand menu select Expressions, then click Expressions and then click the ellipsis.

From the Property drop-down in the Property Expression Editor you need to find the property that would hold the file path for this task. The names are not always obvious and can be called different things in different tasks. In my example I am using a File System Task which I think is the Source Property.

Click the ellipsis in the Expression column and add an expression that consists of your path variable concatenated a string that contains the name of your file. One thing to note is backslashes have special meanings so if your string contains any they must be escaped with a second backslash.

Now when you run the package the For Each will loop through each path you have defined and store it in your variable which tasks will reference.

Inserting .DBF Files from different folder structure with their respective FilePath using SSIS?

1 Answers