Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Insert the new rows from SourceTable to DestinationTable table Using SSIS from two different servers Expand / Collapse
Author
Message
Posted Friday, February 27, 2009 12:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 10:13 PM
Points: 83, Visits: 253
Dear Gurus,

My requirement in to import the new rows from source database to destination database which are in two different servers.

Can anyone please help me in this. how to import.

For example
--------------
Source.
--------------
Select * from Server1.db1.dbo.table1

Pk_Column Column1 Column2
----------- ---------- -----------
1 Value1 Value2
2 Value3 Value4
3 Value4 Value5


Destination
-------------
Select * from Server2.db1.dbo.table1

Pk_Column Column1 Column2
----------- ---------- -----------
1 Value1 Value2


Now i want to insert the two new rows(pk_Column: 2,3) from Source to destination. using SSIS package.

I have to schedule the SSIS package.. for every day end..

please help me in this.

Thank you.
Post #665528
Posted Friday, February 27, 2009 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
You have a couple of options.

One is to use a Lookup transform where you send the "error rows" (rows where a match is not found) on to the Destination Component. You would need to Configure the error mode to redirect row.

The other, which will usually perform better is to insert all the rows into a staging table in the same database and then do a Left Outer Join on the destination.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #665708
Posted Tuesday, March 10, 2009 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 10:13 PM
Points: 83, Visits: 253
HI Jack Corbett,

Thank you for ur reply,

ok I will try with the loopup transform.

Could you please send me the test package using Lookup transform.

thank you.
Post #672558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse