Lookup error redirection

Hi,

I would like to know if the following is possible or if there is another way to implement this. I have a lookup transformation which i check to see if a specific record is stored. If nothing is returned i would like to insert a record into the table and then try the lookup again so that this time it will find the record and continue processing. How could i redirect the data flow to allow something like this to be done. I have tried linking the lookup failure constraint to an oledb destination and then attempted to link this back to the lookup but this has not worked.

Does anyone have any ideas on this?

Thanks in advance,

GrantInstead of using an OLEDB Destination component, use an OLEDB Command component. The Destination component will terminate flow while the OLE DB Command will pass records through.|||I seem to have problems when i try to use this. Should it allow me to use a subquery which obtains a record count from a table and if that returns a 0 then insert values from the input into another table?

Thanks

Grant|||I generally use stored procedures as the SqlCommand property. [Syntax is EXEC dbo.StoredProcedureName ?, ?, ?] One of the reasons I like stored procedures is that on the Column Mapping tab, the parameter names are displayed and it's easier to verify that I have the correct data mapped.|||

Another solution is to use multicast and union components: split the error output of Lookup component, direct one output to Sql or OLEDB Destination component, then merge another output with successful output of Lookup component using Union All component.

Lookup -> Multicast -> Destination
| |
Union All
|

|||

Martin, would you mind explaining in a little more detail?

At present, I use the OLEDB Command quite frequently, but with SQLCommands like, "UPDATE tblTEMP SET TempID = ?, SSN = ?,... WHERE ID = ?" etc. As you mentioned, it's a pain to map all of the fields to generic param0, param1, etc.

So what you are saying is that I can create a SP to "map" the parameters?

I appreciate any advice that you can offer a newbie...