SSIS performance issue: Data load from sqlserver2016 (EC2) - Mysql8.0 (RDS)

• sri2016

  SSC Rookie

  Points: 32

  More actions

  November 4, 2020 at 7:52 pm

  #3804375

  Hi,

  I am trying to Full Load  from Table on sqlserver(2016) to a table on Aurora My Sql database (RDS).The table has 1M records and taking 3 hrs to load it when I run it as a job on server. I am using ODBC 32 bit driver  and ADO.net Provider on the ETL side.

  The same query is taking 1 min to load data from Sql server to a text file.

  Any help is much appreciated!

   

  Thanks

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 4, 2020 at 9:26 pm

  #3804406

  The fact that the load to text file is quick suggests (to me, at least) that one or both of

  • Bandwidth between SQL Server database and RDS
  • The Aurora database

  is probably where the bottleneck is occurring. Probably easier to look at bandwidth first, to gauge what level of throughput you are getting.

  ---

• Jeffrey Williams

  SSC Guru

  Points: 90346

  More actions

  November 4, 2020 at 10:22 pm

  #3804423

  I would also look at the specific driver you are using - I would expect anything going to RDS has an x64 driver and that is the one you should be using.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs