How do you get the SSIS package to insert new records when copying data between servers

Question

I am copying some user data from one SqlServer to another. Name them Alpha and Beta. The SSIS package runs in beta and it receives strings in Alpha that satisfy a certain condition. The package then adds rows to the Beta table. Pretty simple and works great.

The problem is that I only want to add new lines to the beta. Usually I would just do something as simple as ....

INSERT INTO BetaPeople SELECT * From AlphaPeople where ID NOT IN (SELECT ID FROM BetaPeople) 

But this does not work in the SSIS package. At least I don’t know how and what is the point of this question. How can this be done on different servers?

Answer 1

Your example seems simple, it looks like you are adding only new people without looking for changed data in existing records. In this case, save the last identifier in the database.

 CREATE TABLE dbo.LAST (RW int, LastID Int) go INSERT INTO dbo.LAST (RW, LastID) VALUES (1,0) 

Now you can use this to insert the last identifier of the passed string.

 UPDATE dbo.LAST SET LastID = @myLastID WHERE RW = 1 

When choosing an OLEDB source, set the data access mode in SQL Command and use

 DECLARE @Last int SET @Last = (SELECT LastID FROM dbo.LAST WHERE RW = 1) SELECT * FROM AlphaPeople WHERE ID > @Last; 

Notice, I assume that you are using the ID int IDENTITY for your PC.

If you need to track changes in the data of existing records, then in the "last modified" column in each table and store the time of the last transfer.

A different method would be to configure the linked server from Beta to Alpha and run your example without using SSIS. I expect this to be slower and more resource intensive than SSIS.

  INSERT INTO dbo.BetaPeople SELECT * FROM [Alpha].[myDB].[dbo].[AlphaPeople] WHERE ID NOT IN (SELECT ID FROM dbo.BetaPeople) 

Answer 2

The simplest method I used is the following:

• Query Alpha in the original task in the data stream and make entries in the data stream.
• Perform any necessary conversions.
• Before writing to the destination (beta), perform a search corresponding to the identifier column from Alpha to the column in beta. On the first page of the conversion transformation editor, make sure that you select "Redirect lines without matching" from the "Specify how to handle lines with a matching error" drop-down list
• Associate your search task with your destination. This will give you a hint where you can indicate that these are the unique lines that you want to insert.

Answer 3

This is a classic Delta detection problem. The best solution is to use Change Data Capture with / without SSIS. If what you are looking for is once in a lifetime, there is no need to go for SSIS. Use other tools, such as a linked server, and compare with existing entries.

Answer 4

• Add a search between your source and destination
• Right-click the search box to open the conversion transform editor.
• Select [Redirect rows without displaying results.
• 
• Open columns, match key columns,
• Add a table key entry to the search column, the search operation as
• Connect the search box to the destination, select [Search without matching results]

Answer 5

The following should solve the problem of loading modified and new records using SSIS:

• Extract data from usint source. Data stream.
• Retrieve data from the target.
• Match on Primary key Add Unmatch records and separate matched and unmatched records from source and matching records from Target, call them Matched_Source, Unmatch_Source and Matched_Target.
• Compare Matched_Source and Matched_Target and Split Matched_Source with modified and unchanged.
• Zero-load TempChanged table.
• Add modified entries to TempChanged.
• Executing a SQL script / saved proc to remove records from the target for the primary key in TempChanged and add records to TempChanged to Target.
• Add Unmatched_Source to Target.