Firstly, all my data\instances etc are sat on AWS Cloud. I'm using SSIS to extract data from various databases and send to a different database. I have 2 different sources:
- SOURCE A - A table in DATABASE A on AWS Aurora PostgreSQL (Pg1)
- SOURCE B - A table in DATABASE B on MS SQL Server (Sql1)
The Destination - a couple of tables in DATABASE C on AWS Aurora PostgreSQL (also Pg1 as above).
I am using an ODBC (driver installed) to connect to the Pg1 instance and Integrated Security to connect to Sql1. All connection managers are using ADO.NET connection types.
Now I know there will be lots of other factors here including VPN setup in AWS but the data transfer between databases seems a bit slow. I have a little under 1 million rows in DatabaseA. It takes about 30 minutes to Extract\Load that to DatabaseC. DatabaseB has 8 million rows and takes between 5-6 hours to Extract\Load.
Straight off the bat, is there anything I can specifically look at to try and speed up the Extract\Load of data?
I'm guessing the problem is with using ODBC drivers (although I am not sure what else I can use within SSIS). eg I have used AWS Glue for other jobs and loading data to Postgres takes a fraction of this time. I can provide any extra information as necessary if it would help understand the setup I am using!
