SQL Server Integration Services

Question

SQL Server Integration Services

Cindy Vo-Lake 0

How to link data from two databases on two different servers?

The goal is to get this query into a table on a third database using a join on a unique identifier and several filter criteria. One of the source databases has millions of records.

I have attempted to execute this by creating two OLE DB Sources feeding into a Merge Join with an OLE DB Destination. However, I get 0 records written in the OLE DB Destination. Please see Data Flow diagram below. DB 2 and DB 3 are on the same servers. DB 1 on Server 1 has millions of records. The Data access mode for DB 1 is using a SQL command text. I am to preview the SQL command successfully. The SQL command text is:

SELECT Column A, Column B, Column C, Column D, Column E, Column F

FROM DB 1

WHERE (COLUMN C > '1/1/2020') AND (COLUMN X > '10/01/2023') AND (COLUMN Y = 'USA')

User's image

The current Merge Join is using an inner join. I have also set the IsSorted property to True for the OLE DB Source Output and set the SortKeyPosition to 1 for one of the Output Columns.

User's image

Some of the results in the Output Debug are listed below:

Information: 0x4004800C at Data Flow Task, SSIS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

SSIS package "C:\Users....\Package.dtsx" finished: Success.

The program '[9752] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

If there are more efficient work flows, please advise. Any guidance is much appreciated. Thank you for your time!

Very Best,
Cindy

1 answer

Your answer

Answer 1

Yitzhak Khabinsky 26,586

Hi @Cindy Vo-Lake,

DB 1 on Server 1 has millions of records.

Temporarily just for test, you can try to limit data volume from the DB1:
SELECT TOP(10000) * FROM ...;
Because JOIN is done in SSIS Data Flow Task (DFT) that machine will need more available RAM memory. No question about it.

Cindy Vo-Lake 0 Reputation points

2023-11-01T14:27:37.6366667+00:00

Hi @Yitzhak Khabinsky,

Thank you for reviewing my question.

I tried what you instructed. I am getting 0 rows. Maybe because the first 10,000 rows in DB 1 does not have a match in DB 2? How do we solve needing more available RAM memory issue? I have been running this package on my local laptop. Should I try on one my Azure Virtual Machines?
Yitzhak Khabinsky 26,586 Reputation points

2023-11-01T17:14:01.9733333+00:00
@Cindy Vo-Lake,

It is very possible that the first 10,000 rows don't have a match to the data from the DB 2. You can add SSIS Data Viewers to visually inspect what data is flowing in the SSIS pipeline.

I added a next method to try below. You can use a pagination method via T-SQL. Just change the @BatchNo parameter value for your needs.

Obviously, your development laptop has not enough RAM memory for the entire data volume. I wouldn't worry to much about it. Your SSIS run-time server should have enough resources to process the entire data set with millions of rows.

DECLARE @BatchNo INT = 1 , @RowsPerBatch INT = 10000; SELECT * FROM @tbl ORDER BY ID OFFSET (@BatchNo - 1) * @RowsPerBatch ROWS FETCH NEXT @RowsPerBatch ROWS ONLY
Cindy Vo-Lake 0 Reputation points

2023-11-02T12:55:43.1866667+00:00

Yitzhak Khabinsky,

I tried your T-SQL command in several areas.

In SSIS/MS Visual Studio. Program froze. I let it think for approximately 20 minutes. Screenshot below:

I executed the T-SQL command in SSMS in DB 1 Server 1. Query took more than 5 minutes. No records returned. This db is mostly created with views. Maybe this is why query in SSMS and SSIS does not work?

I executed the T-SQL command in SSMS in DB 2 Server 2. Records returned instantly.

Please advise. Thank you!
Yitzhak Khabinsky 26,586 Reputation points

2023-11-02T13:03:21.93+00:00

@Cindy Vo-Lake,

It seems that your view (or underlying table) is in need of an index on the column that is used for the ORDER BY clause. You can check that by checking Query Execution Plan in SSMS. It might even suggest you a missing index.
Cindy Vo-Lake 0 Reputation points

2023-11-02T14:23:26.3633333+00:00

@Yitzhak Khabinsky,

I believe I do not have permissions to view this in DB 1 Server 1. This is a third party database I am trying to extract data from.

Msg 262, Level 14, State 4, Line 8

SHOWPLAN permission denied in database 'DB 1 SERVER 1'.

Completion time: 2023-11-02T09:09:48.3730595-05:00
Yitzhak Khabinsky 26,586 Reputation points

2023-11-02T14:27:32.1966667+00:00

@Cindy Vo-Lake,

You can try to change ORDER BY ... clause to a Primary Key column. It should have an index on it.
Cindy Vo-Lake 0 Reputation points

2023-11-08T15:30:51.29+00:00
Hi @Yitzhak Khabinsky ,

The query below worked when I used ORDER BY with a Primary Key Column:

DECLARE @BatchNo INT = 1 , @RowsPerBatch INT = 10000; SELECT * FROM @tbl ORDER BY PRIMARY KEY COLUMN OFFSET (@BatchNo - 1) * @RowsPerBatch ROWS FETCH NEXT @RowsPerBatch ROWS ONLY

What is the next step to get matching records from DB 1 SERVER 1 AND DB 2 SERVER 2 into DB 3 SERVER 2?

Does Microsoft provide in-person SSIS training anywhere in Houston, TX? I need an in-person SSIS walk through. Thank you.
Cindy Vo-Lake 0 Reputation points

2023-11-08T15:34:52.7866667+00:00

testing.

Share via

SQL Server Integration Services

1 answer

Your answer