SSIS - Using a foreach loop container to update records after transfer

Question

We are using SSIS to transfer the contents of 3 tables from ServerA to ServerB

ServerA is located inhouse, ServerB is in a datacenter. There is VPN connectivity from ServerA to ServerB, and vice-versa. For security reasons, we are looking to remove the ability for ServerB to “see” ServerA

The current SQL select statement goes something like this:

SELECT * FROM ServerB.OrderTable WHERE NOT IN ServerA.OrderTable

(i appreciate the syntax is off)

These records are then inserted on ServerA.OrderTable (the table is identical)

This works great- only transferring records that are not in ServerA

However, this requires ServerB to be aware of ServerA What I propose to do is put a “Transferred” bit column on each table, and loop through each record, setting Transferred to true.

That way, the above mentioned SQL statement could be changed to something like:

SELECT * FROM ServerB.OrderTable WHERE Transferred = 0

My question is, how to go about doing this? I have been advised that a foreach loop container could do this, but I can’t find that anywhere... Could someone point me in the right direction

You may be over-complicating this. Can you not have server A create a very narrow table consisting only of the primary keys called something like `[ServerAPK]`, transfer it to Server B, and use that list in your `WHERE` clause? — JNK, Jan 06 '11 at 13:18
Why don't you just use the MERGE command? It is designed to sync two tables. You don't need to worry about setting bits, loops, or anything much else. http://bit.ly/7UU0p — IamIC, Jan 07 '11 at 16:30
Because one server is SQL 2008, one is 2000 - this is only supported > 2008 — Alex, Jan 10 '11 at 17:42

score 3 · Accepted Answer · edited May 23 '17 at 12:19

Probably, you have found an answer to your question by now. This answer is to help others who might stumble upon this question. Here is a possible option that can be used to resolve the data transfer using SSIS. I assumed that you can still create connection strings pointing to both your servers A and B from the SSIS package. If that assumption is wrong, please let me know so I can delete this answer. In this example, I am using SQL Server 2008 R2 as back-end. Since I don't have two servers, I have created two identical tables in different Schemas ServerA and ServerB.

Step-by-step process:

In the Connection manager section of the SSIS, create two OLE DB Connections namely ServerA and ServerB. This example is pointing to the same server but in your scenario, the connections will need to point to your two different servers. Refer screenshot #1.
Create two schemas ServerA and ServerB. Create the table dbo.ItemInfo in both the schemas. Create scripts for these tables are given under Scripts section. Again, these objects are for this example only.
I have populated both the tables with some sample data. Table ServerA.ItemInfo contains 2,222 rows and table ServerB.ItemInfo contains 10,000 rows. As per the question, the missing 7,778 rows should be transferred from ServerB to ServerA. Refer screenshot #2.
On the SSIS package's control flow tab, place a data flow task as shown in screenshot #3.
Double-click on the data flow task to navigate to the data flow tab and configure the data flow task as described below. Server B is an OLE DB Source; Find record in Server A is a Lookup transformation task and Server A is an OLE DB Destination.
Configure OLE DB Source Server B as shown in screenshots #4 and #5.
Configure Lookup transformation task Find record in Server A as shown in screenshots #6 - #8. In this example, ItemId is the unique key. Hence, that is the column used to search for missing records between the two tables. Since we need only the rows that do not exist in Server A, we need to select the option Redirect rows to no match output.
Place a OLE DB Destination on the data flow task. When you connect the Lookup transformation task with OLE DB Destination, you will be prompted with Input Output Selection dialog. Select Lookup No Match Output from the dialog as shown in screenshot #9. Configure the OLE DB Destination Server A as shown in screenshots #10 and #11.
Once the data flow task is configured, it should look like as shown in screenshot #12.
Sample execution of the package is shown in screenshot #13. As you can notice, the missing 7,778 rows have been transferred from Server B to Server A. Refer screenshot #14 to view the table record count after the package execution.
Since the requirement was to just insert the missing records, this approach has been used. If you would like update existing records and delete records that are no longer valid, please refer the example that I have provided in this link. SQL Integration Services to load tab delimited file? The example in the link shows how to transfer a flat file to SQL but it updates existing records and deletes invalid records. Also, the example is fine tuned to handle large number of rows.

Hope that helps.

Scripts

.

CREATE SCHEMA [ServerA] AUTHORIZATION [dbo]
GO

CREATE SCHEMA [ServerB] AUTHORIZATION [dbo]
GO

CREATE TABLE [ServerA].[ItemInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    CONSTRAINT [PK_ItemInfo] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UK_ItemInfo_ItemId] UNIQUE NONCLUSTERED ([ItemId] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [ServerB].[ItemInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    CONSTRAINT [PK_ItemInfo] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UK_ItemInfo_ItemId] UNIQUE NONCLUSTERED ([ItemId] ASC)
) ON [PRIMARY]
GO

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

SSIS - Using a foreach loop container to update records after transfer

1 Answers1

Linked