SSIS based on filename mapping to Database table

Question

Post reply

SSIS based on filename mapping to Database table

vibin.das

Say Hey Kid

Points: 705
More actions
July 6, 2010 at 7:33 am

#145847

Hi,
I'm totally new to SSIS 2005 and i have been assigned to do the following tasks.
Following is the scenario
1)There will be a root folder say A1 and inside that there will be different folders say A1.1 & A2.1
2)The above folders A1.1 & A2.1 will be having 4 csv files name starts with BVI,BGC,ILV,ILC
Requirement
-------------
1)I need to loop through files inside folders A1.1 & A2.1
First i have to loop through A1.1 and the files inside that and based on the file name inside the folders i need to map to tables in DB
EX-if the file starts with "BVI" need to map to table C1
-if the file starts with "BGC" need to map to table C2
Similarily for folder A2.1
How can these be accomplished.I'm really confused.SSIS experts please help me on this.It's urgent.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Vibin,

Out of box SSIS components will not meet your need.

You will have to code script tasks that meet your need

Raunak J

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

vibin.das (7/6/2010)
Hi,
I'm totally new to SSIS 2005 and i have been assigned to do the following tasks.
Following is the scenario
1)There will be a root folder say A1 and inside that there will be different folders say A1.1 & A2.1
2)The above folders A1.1 & A2.1 will be having 4 csv files name starts with BVI,BGC,ILV,ILC
Requirement
-------------
1)I need to loop through files inside folders A1.1 & A2.1
First i have to loop through A1.1 and the files inside that and based on the file name inside the folders i need to map to tables in DB
EX-if the file starts with "BVI" need to map to table C1
-if the file starts with "BGC" need to map to table C2
Similarily for folder A2.1
How can these be accomplished.I'm really confused.SSIS experts please help me on this.It's urgent.

It's not easy. You'll need a dataflow for every destination table (assuming the table structures are all different).

Use a Foreach loop to iterate around the files.

Depending on the name of the file being processed, you'll need to branch to the relevant dataflow using precedence constraints.

This is not the sort of task to learn SSIS on!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vibin.das Say Hey Kid Points: 705 More actions · Answer 3

Hi,

Thanks for the reply.Do u have samples for looping through the files inside folders and also the precedence contraint.It would be a great help for me.