SQLTeam.com | Weblogs | Forums

Ssis help

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.

  1. Is this a one time migration from sql server to postgres?
  2. Is it continuous process that happens weekly or daily?
  3. where is the postgres server located at physically/virtually in relation to the sql server? And are you pushing the data using a linked server to postgres or using the ado.net connection?
  4. Is the source database/tables OLTP, meaning while you are doing the transfer of data, the source tables are being used by other systems?

In addition to @yosiasz questions:

Have you identified which component is taking the most time - the source or the destination?
How long does the query from the source system take when run directly in SSMS?
Can you monitor the load process in PostgreSql?
Does the destination (target) table in PostgreSql have a lot of indexes? How large is the destination table?
Are you performing an UPSERT in PostgreSql or just a straight insert?