How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J