Post reply

Conditional Split query

  • 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

  • September 23, 2007 at 9:18 pm

    #735511

    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

  • September 23, 2007 at 11:07 pm

    #735520

    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

  • September 24, 2007 at 2:25 am

    #735532

    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

  • September 24, 2007 at 4:29 am

    #735557

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

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

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