MSSQL Import and Export Data tool (DTSWizard) to import data to PostGreSQL 9 from MSSQL 2008R2
Source table 24 column primitive data type 1 column nvarchar(4000) - 3.7M rows
Where I try to use the wizard to replicate a table from MSSQL to PostGreSQL it either performs very slow or crashes.
Tried 32bit DTSWizard with Bulk Insert Method: Value Sets Buffer Size:1 - very slow 50K rows in about an hour
Tried 32bit DTSWizard with Bulk Insert Method: Value Sets Buffer Size:200 - application crashes in a few seconds
Tried 64bit DTSWizard with Bulk Insert Method: Copy Command Buffer Size:10 - Consumed 10G ram after 250K rows causing system unresponsiveness due to memory exhaustion
Same with Pipe/Copy and any value for Buffer Size.
What is the best way method and setting to use to duplicate data from MS SQL to PostGreSQL. My intention is to duplicate the data for development work but then to used Linked Server Replication once in production.