I need to load the data from a Oracle server in another country by using SSIS package. I've configured Oracle drivers, environment variables and connections in SSDT already. I'm loading about 200 MB of data (one table) and it takes about 20 minutes! to load the data. The link in the remote location is 15Mbps with 86ms of Latency. Is there a way to configure the Oracle or SSIS package to speed up the load ?
You should get some significant performance improvement using the FetchSize connection string property. The Oracle documentation for the max value is here.
FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.
But mainly I would recommend upgrading to SSIS Enterprise edition and using this connector. Here is the one for SSIS 2014 but you can search for other versions easily. The performance of that connector is much improved.