0

I have data in postgres that I need to programmatically move to sql server on a scheduled basis. The tables have the all have same schema and the tables are not very relational (no foreign keys), but I think I can just use WITH NO CHECK if there are.

I have ADO.Net connectors for both servers, so I think I can just select from the postgres to a DataTable and then INSERT INTO the SQL SERVER tables. However, I'm a little concerned on performance.

My question is how hard would it be to use the Postgres COPY command to export to say CSV, and then use bcp.exe to import the CSV into SQL SERVER. Does this sound like the way to go? I can see this needing to scale to ~750k records being copied per schedule.

Dave
  • 1,645
  • 2
  • 23
  • 39
  • You might also be able to create an SSIS package to do this, which would probably perform much better. Take a look at https://slalomdotcom.wordpress.com/2011/03/14/connecting-to-a-postgresql-instance-using-sql-server-integration-services/ – DWright May 07 '15 at 17:22
  • How much can you change an SSIS package from use to use programatically? I need to change the search path for each copy (there are many schema's we are selecting from) on postgres. – Dave May 07 '15 at 17:37
  • Take a look at: http://stackoverflow.com/questions/273751/how-to-execute-an-ssis-package-from-net – DWright May 07 '15 at 21:13

0 Answers0