SSIS package to hold Data or result set and looping the same

Question

SSIS package to hold Data or result set and looping the same

Pulivarthi Sasidhar

SSCertifiable

Points: 6706
More actions
September 8, 2011 at 11:04 am

#247768

Hi All,
Suppose if imported 100 rows[ with RefNo] from FlatFile to Local DB table[which has IDentity proprty], can i get the generated id for each rows with RefNo in Table inside SSIS [Not in SQL TempDb or Database Table]?
Once i get the table can i loop the same table in For / Foreach loop?
Is it possible to Import another Flat FIle based on the refNo availed in the above table inside loop in SSIS package?
The above is As is process to develop package....
But i did alternative as follows
Imported two flat files into two[TblA,TblB] tables into Server DB tables.
Executed the Proc with following code...
Inserted TablA data into Original table[Destination1] and inserted the generated ID value with RefNo into #temp table by Cursor/looping the First table.
Inserted TabB with TablA generated ID into Original Table[[Destination2]
(Joined the TablB with #temp on RefNo and inserted the TablA ID in TablB instead of REfNo )
Please give the steps if the Scenario said above is possible?

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

In the workflow, after you import the first file, add an Execute SQL Task, with a full recordset. Store that in a local object variable, and create a For Each Next loop that steps through the dataset in the variable.

I do that kind of thing all the time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 2

Thanks....

How do i import data into Tabl2 from Fil2 in For each loop for particular RefNo

Suppose data set has 10 ref no's

For first iteration refNo is 1123

In this iteration Fil2 of records with 1123 as refno only needs to Import into Tabl2 with corresponding Identity value(generated for Fil1 insertion into Tab1 for 1123 ref no), left remaining all records in Fil2 (i,e Not matched with RefNo1123)

kramaswamy SSCoach Points: 18181 More actions · Answer 3

You asked basically the same question in another thread.

Like I indicated in that thread, your best bet is to use the Lookup task.

You have only one file, which you want to import data from. Fil2 goes into Tabl2.

So you use a Lookup against Tabl1, and choose to redirect the error, which you don't need to bother attaching an output to.

This way the only records which get inserted into Tabl2 are those which have a corresponding lookup match in Tabl1.

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 4

I tried with Lookup and not able to get this.

I did the following in my Package

Data Flow task1

File1 Source=> OLEDB Destination => Lookup Task

Data FlowTask2

File2 Source=>OLEDB Destination

Where can i get Tbl1 Generated ID in Lookuptable and i was not able to get the used Lookup task in Dataflowtask1 inside of DataFlowTask2

If my process is wrong please give the steps as above mentioned

I didnt use Lookup task till now...so i am litle bit fuzzy abt it...

Thanks....

kramaswamy SSCoach Points: 18181 More actions · Answer 5

It should be like this:

File2 Source=>Lookup=>OLEDB Destination

Where the Lookup is going to query Table1, and retrieve the ID field as well as the field that File2 will match it on.