June 28, 2016 at 10:30 pm
Hello, my SSIS got performance issue. It gets data from Oracle 11g:
OLE DB Source
SELECT ..., ..., ... FROM oracle_table_a
INNER JOIN oracle_table_b ...
INNER JOIN oracle_table_c ...
SQL Server Destination
then INSERT INTO SQL 2014 staging table, no index.
No. of records: about 5,308,000
Its daily (04:00 AM) execution duration could varies greatly: 30 min to 1.5 hour or even 2 hours.
I don't know how to explain such variation to my users.
Where is the bottle neck? Oracle 11g SELECT ? Or SQL 2014 INSERT ?
Both of them are inside the SSIS Data Flow Task item.
How can I get the exact SQL 2014 INSERT start time within the Data Flow Task item?
Any idea?
June 28, 2016 at 11:33 pm
Hope the following link helps you to understand how to track the memory utilization of a running package.
June 29, 2016 at 12:16 am
Well there can be many scenarios for example one possible case can be that you are running your package on the Db machine, and there is not enough memory for SSIS.
You can also check if there are any sub subsequent jobs/load is working parallel to this package. If the load is source intensive this can also cause slowness.
Are you using Linked Server to fetch data over the internet? This could also cause slowness.
OR
if you are fetching the data direct from source, in this case Which drivers you are using for Oracle 11g?
Using Attunity drivers can speed up things following are few links:
microsoft-connectors-by-attunity
Using the Microsoft Connector for Oracle by Attunity with SQL Server
For tracking the SSIS execution duration log check the following :
how-to-get-the-start-time-and-end-time-of-process-ssis
tracking-ssis-tasks-execution-duration
Logging – Level 11 of the Stairway to Integration Services[/url]
Advanced Logging - Level 12 of the Stairway to Integration Services[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply