Netezza to SQL Server Data migration

Question

I have a Netezza DB with one table of 4 billion records. I am working on a migration project to move the data to SQL Server. I created a simple SSIS ETL but that is running for a very long time and stops due to buffer memory issue. What is the efficient quicker way(s) of transferring such huge amount of data?

Answer 1

You can try to separate the source data into batches, for example 1,000,000 rows(depends on your memory) a batch to merge into Netezza table.

DECLARE @BatchSize INT = 10000

    WHILE 1 = 1
    BEGIN

        INSERT INTO [dbo].[Destination] --WITH (TABLOCK)  -- Uncomment for 2008
        (
            FirstName
            ,LastName
            ,EmailAddress
            ,PhoneNumber
        )
        SELECT TOP(@BatchSize) 
            s.FirstName
            ,s.LastName
            ,s.EmailAddress
            ,s.PhoneNumber
        FROM [dbo].[SOURCE] s
        WHERE NOT EXISTS ( 
            SELECT 1
            FROM dbo.Destination
            WHERE PersonID = s.PersonID
        )

        IF @@ROWCOUNT < @BatchSize BREAK
        
    END