SSIS Data Transference Slow

Question

Hi,

I am Running an SSIS package (SQL 2019) from an EC2 instance that is simply extracting data from an Oracle instance and writing the data to an fSX location. Data is transferring very slowly. Process has been running in excess of 6 hours when it should only take an hour. It has only transferred 4 out of the 11 GB of data. Previous attempts have taken well over 20 hours and even then, failed to complete.

When running an extract query, the query does return some 119 columns to the rows are very wide. I ran a test where I greatly reduced the number of columns returned and I was able to run the query with out issue. Returning 18 million rows in just under 15 minutes. It seems that the issue is not with how many rows but with how wide those rows are. Is this a known problem with SSIS packages?

Answer 1

That's to less on detailed information, I only see that at least 3 components are involved: SSIS, Oracle and fSX location; so the cause can come any of them.
I transfer every night more then 200 GB of data between serveral MS SQL Server within 2 hour with SSIS; so on first hand I would exclude SSIS here.

Answer 2

If you add TO_CHAR() around every column, will it help reducing the time?

Answer 3

Hi @Robert Wright ,

Basic performance tuning of your databases, tuning of the statements involved, and possibly even tuning of the operating systems on each server would be needed.

Enable process monitor and profile trace to see the issue related with SQL Server.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.