SSIS Data Flow Task EXCLUDE Duplicate Data

Hi,

I am very new to SSIS.  I have a data flow task.  
My Source is: ServerA.TableA,
My Destination: ServerB.TableB.  

I wish to only bring data from ServerA.TableA where it is NOT already in ServerB.TableB.

How do I do this please?

Thnaks

nutnut
nutnutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bradleys40Commented:
Is there other data in table B or is it just an "older" copy of table A if it is you could just clear the contents before the transfer in table A data
0
nutnutAuthor Commented:
Hi yes there is data already in tableB and so unfortunately I cannot delete it
0
bradleys40Commented:
are you using dts or ssis
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nutnutAuthor Commented:
SSIS
0
bradleys40Commented:

if you create the sql for the data transfer based on an  NOT EXISTS command
INSERT INTO dbo.Target
SELECT *
FROM dbo.Source AS s
WHERE NOT EXISTS
(
      SELECT 1
      FROM dbo.Target AS t
      WHERE t.title_id = s.title_id
)

this should add only new data
0
nutnutAuthor Commented:
They are on different servers though, how would I build this into a Data flow Task
0
Pratima PharandeCommented:
I had same problem recently

you can solve it like this

1.Source control with ServerA.TableA , connect it to
2. Add Lookup contorl & Set ServerB.TableB to table property of it . Second tab of Lookup -map the primary keys there and get all other columns from avilable lookup columns as output . connct this lookup to Conditional Split
 3.  Conditional Split -> In columns you will see all input and output columns , add one condtion to comapre all this with and , for true condtion is duplicate records
 4. Default condition goes to destination
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nutnutAuthor Commented:
Thats great thanks but cannot get it to work, I think the prob is with my Condition Split, can you elaborate on what I should be doing within it please, what conditions should I be putting within it?
0
Pratima PharandeCommented:
ok

Consider

ServerA.TableA  have ID (PK) and Description1 , Description2  these are the fileds....
same in ServerB.TableB

In lookup map both primary keys.then

you are selecteing ServerB.TableB Description1 , Description2 rename it as Description1_out , Description2_out as shown in the image attached...

then in the condition split add the condition like
DESCRIPTION1 != TRIM(DESCRIPTION1_out) && DESCRIPTION2 != TRIM(DESCRIPTION2_out)

as shown in second image

then connect it to destination
condition.JPG
lookup.JPG
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.