How to improve performance while loading 200,000 records data from sql server to postgres using ssis package
It's taking 2 hours time for loading records 200,000 from sql server to postgres
source sql server table: emp and fields :30 its mixed int,varchar,decimal daatypes
target postgres table : emp and fields :30 its mixed int,varchar,decimal daatypes
for performanc steps i followed like below:
default buffer size i have increase 10 mb to 30 mb
default rows: 10,000 rows to 30,000 rows
select required filed in the source select statement using oledb source
destination i have using odbc destination for configure the postgres table
no data types changes while loading sql server to postgres we have maintain exact sql server and postgres dbs. its one to one loading (sql server to postgres no business logic)
in ssis using: oledb source for sql server tables using select statement and select required columns odbc destination for postgres tables and select destination table and created ado.net connection for dsn configure.
we can do bulkinsert task using ssis ,but its support only text files to sql server tables.
same thing can we do sql server to postgres tables loading using bulk insert task.
can you please tell any solution to reduce loading time from sql server to postgres loading using ssis package.