Conditional Split query

Question

Conditional Split query

lalit-450914

SSC-Addicted

Points: 462
More actions
September 23, 2007 at 3:03 am

#118670

Hi,
I have the following table in MsAccess
EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each row in EmployeesA using a foreach file loop?

Thanks,
lolsron

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

The title of your post reads "Conditional Split query", but your post doesn't mention how you are using this component, if at all. The problem you describe can be resolved using the conditional split. Include a conditional split component between the retrieval logic from Access and the processing logic on the SQL Server side. Redirect the rows according to the three possible values you describe, and handle each subset of rows accordingly.

hth

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

I assume that in your foreach task, you have a data flow task that has, as its source, an OLE DB source with a SELECT statement on the MS-Access table and an OLE DB destination.

You will need to add a conditional split transform after the OLE DB source that splits based on the 3 values for empStatus.

For status A, the output from the conditional split should be sent to an OLE DB destination (probably similar to the existing one)

For status D, you should then do a Lookup and if found, you then use an OLE DB Command tranform to do the update

For status S, you will need a lookup, OLE DB Command and an OLE DB Destination

lalit-450914 SSC-Addicted Points: 462 More actions · Answer 3

Hi,

Excellent. Thanks for understanding the problem so clearly.

How to work with the Lookup conditions. I have to pass empid,age and see if the record is present. If yes, update the record. Will i use a sql statement? How to pass the current row to the sql?

thanks so much for yur help

lalit-450914 SSC-Addicted Points: 462 More actions · Answer 4

lalit-450914

SSC-Addicted

Points: 462

September 24, 2007 at 4:29 am

#735557

I solved it..thanks for all the help happycat 🙂