SSIS: How to delete rows from a resultset before using the OLE DB Destination?

Question

Hello!

I have a problem using the SSIS. I try to import data from database one to database two. Therefore I use a OLE DB Source with the following command:

SELECT Name, Description, JobID
FROM   DBone.Table

My problem is, that I only want to import new data from this source. So I use a second OLE DB Source withe the following command:

SELECT JobID
FROM   DBtwo.Table

Now I want to import only the data from database one, where the JobID is not in database two. So my idea was to use a Merge Join for the two data sources, so that my data looks like this.

Name | Description    | DBone.JobID | DBtwo.JobID
-----------------------------------------------
'One'| 'Little test.' |      4      |     4    
'Two'| 'Little test.' |      6      |    NULL

So I have the possibility to identify new data, because the column DBtwo.JobID is NULL.

Now my question: How do I say SSIS to import only the data into database two where the DBtwo.JobID is NULL or to delete this data before import?

Answer 1

I think I can answer my question by myself, because I find a way to filter the data.

After I merged the two tables using the Merge Join Component I added a Script Component. In this component I set the ExclusionGroup for the output from 0 to 1 and added the follwing code to the script:

If Row.JobID_IsNull Then

   Row.DirectRowToOutput0()

End If

So the Script Component filters the data.

Answer 2

An alternative approach might be to first run an Execute SQL Task to get the latest JobId from DBTwo, store that result in a variable, then use the variable in the SQL command for DBone.

Answer 3

I would have used a conditional split. Send one group of records down the path to be inserted and send the other to the trash destination (or just not define where the other goes to).

For the trash destination see this link: http://www.sqlis.com/post/Trash-Destination-Adapter.aspx

ONe thing I find especially useful about this is that I can set up a data viewer to see what records I'm trashing. It is also clear for maintenance purposes that I intended to throw those records away and didn;t just forget to set up another path for the records following theother part of the split.

Trash destination is also useful for using as your data destination when you want to test things before actually putting them into the real destination.