Search code examples
sql-serverssisoledbetllookup

Oledb command executing even if no rows from lookup

I have a SSIS package where from look up if no match need to insert those records as flag Y but if present previously I have to update those records present to flag to N.The checking is done based on code.Lookup settings is as follows

enter image description here

I have built the SSIS package as folows:

enter image description here

OLE DB command is used to update the flag to N where it is Y (for existing records) based on code that comes from lookup no match. OLE DB destination is used to insert the new records that comes from no match from lookup with flag as Y. I want to correctly write the OLE DB Command so that it only update those records flag as N which are already Y and they are coming from lookup no match output. But how to form the query correctly I am facing issue.I wrote this

UPDATE [LKP].[l_channel_hierarchy]
SET record_end_date = CONVERT(VARCHAR,DATEADD(SECOND,-1,GETDATE()),120) 
,current_record_flag   = 'N'            
WHERE current_record_flag = 'Y' AND [CHANNEL_CODE] = ?

The above query I built to achieve the same where it will update flag to N taking the rows from look up no match as parameter .

enter image description here

Solution

  • If you are trying to "if no match need to insert those records as flag Y but if present previously I have to update those records present to flag to N", then Lookup No Match Output will have only missed records, and the observed behavior (not matched records are fed into the OLE DB Command) is valid.
    To set current_record_flag of existing records to N, fetch another dataflow from the Lookup transform named Lookup Match Output, and put the OLE DB Command there.