Need help to update record with foreach loop in SSIS

Question

I'm working on data cleansing project where I'm executing 1st stored procedure to get all the data which has issue and store it into a staging table1 with ID, IND_REF, CODE.

Table structure is:

ID | IND_REF|CODE
12 |  2333  |ABC
13 |  1222  |EFG

Now each code associated with IND_ref is primary key of the table2 and email table where data will be updated.

Next I wrote another stored procedure with an IF statement stating,

• If code = ABC then update school email as main email where emailtable_ID = staging table IND_REF

Once it update all the row of email table by reference of staging table IND_REF I used another if statement,

• IF code = 'EFG' do that.... where table2_ID = staging table IND_REF...

and so on..

Basically I want to update the row of live table by referencing CODE associated with each IND_REF...

Can I achieve this with a SSIS package? Can I loop through the staging table to update the live table? Any help would be much appreciated. I am new to the SQL world so I find it difficult to loop through each record by setting counter to update live table. any help with script would be very helpful.

Answer 1

I don't understand your issue but let me show you an example:

If we have a table like this:

TABLE1
ID    ind_ref    code
1        1        ABC
2       15        DEF
3       17        GHI

and a table like this:

TABLE2
ind_ref2  code
1         ZZZ
2         XXX
3         DDD
4         ZZZ
5         XXX
15        FFF
17        GGG

Then if we run this query:

UPDATE TABLE2
SET Code = TABLE1.Code
FROM TABLE1
WHERE TABLE1.ind_ref = TABLE2.ind_ref2;

Table 2 will end up like this:

TABLE2
ind_ref2  code
1         ABC    <= I got updated
2         XXX
3         DDD
4         ZZZ
5         XXX
15        DEF    <= me too
17        GHI    <= and me

If this is not your data or your requirement, please take the time to lay out examples as I have: explain the data that you have and what you want it to look like.

Note: SSIS is not required here and neither is looping.