Using SSIS 2012 - merge join component to transfer data to destination provided it does not exist

HI Folks,

I have a table - parts_amer and this table exists in source & destination server as well.

CREATE TABLE [dbo].[Parts_AMER](
 [ServiceTag] [varchar](30) NOT NULL,
 [ComponentID] [decimal](18, 0) NOT NULL,
 [PartNumber] [varchar](20) NULL,
 [Description] [varchar](400) NULL,
 [Qty] [decimal](8, 0) NOT NULL,
 [SrcCommodityCod] [varchar](40) NULL,
 [PartShortDesc] [varchar](100) NULL,
 [SKU] [varchar](30) NULL,
 [SourceInsertUpdateDate] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Parts_AMER] PRIMARY KEY CLUSTERED
(
 [ServiceTag] ASC,
 [ComponentID] ASC
)
)

I need to exec the following query using SSIS components so that only that data ,is transfered,which does not exist at destination -

select source.*

from parts_amer source left join parts_amer destination

on source.ServiceTag = destination.ServiceTag

and source.ComponentID=destination.ComponentID

where destination.ServiceTag  is null and destination.ComponentID is null

Question - Can Merge component help with this?

Pl help out.

Thanks.

June 30th, 2014 3:49am

You can also achieve this by Look Up, but you have to make sure you don't have duplicate rows from you're source.
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2014 3:53pm

Hi Rvn_venky2605,

The Merge Join Transformation is used to join two sorted datasets using a FULL, LEFT, or INNER join, hence, not suitable in your scenario. As James mentioned, you can use Lookup Transformation to redirect the not matched records to the destination table.

Another option is to write a T-SQL script that makes use of Merge statement, and execute the script via Execute SQL Task.

References:

Re

July 4th, 2014 6:10am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics