ronedin


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 table each row in EmployeesA using a foreach file loop

Thanks,

ron




Re: Conditional Split query

jwelch


If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.







Re: Conditional Split query

ronedin

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

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.

Which control to use. Where to specify etc.

thanks,






Re: Conditional Split query

ronedin

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. How to pass the current row

thanks.





Re: Conditional Split query

DarrenSQLIS

Do you mean how to update the row that was matched in the lookup Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.






Re: Conditional Split query

ronedin

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks





Re: Conditional Split query

DarrenSQLIS

An example, do you mean for this problem-

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'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname =

AND age =

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.






Re: Conditional Split query

ronedin

Darren,

you know what..that worked like a charm Smile i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2178311&SiteID=1

thanks.