Optimizing OLE DB Destination for Fast load from Oracle to SQL Server for SSIS
I'm working with a SSIS package for importing from an Oracle Table to an SQL Server Table. for this in between I had to put a data conversion.
the OLE DB Source is retrieving the complete Table, then being converted by the data conversion and then sent to the OLE DB Destination with current setup
now, the table I'm trying to import has around 7.3 Million records with 53 columns.
I need to know how can I setup (or what changes should do to current setup) to speed up as much as possible this process.
This package is going to run scheduled as a job in the SQL server agent.
In the last run inserted 78k records in 15 minutes. at this pace is too slow.
I believe I have to tune setting with the "rows per batch" and "maximum insert commit size" but looking around I haven't found information about what settings should work, and I've tried different settings here, not finding actual difference between them.
UPDATE: After a bit more test, the delay is from getting records from Oracle, not to insert them into SQL server. I need to check on how can I improve this
I think that the main problem is not loading data into SQL Server, check the OLE DB provider you are using to extract data from Oracle.
There are many suggestions you can go with:
- Use Attunity connectors which are the fastest one available
- Make sure you are not using the old Microsoft OLEDB Provider for Oracle (part of MDAC). Use the Oracle Provider for OLEDB (part of ODAC) instead
- If it didn't work, try using an ODBC connection / ODBC Source to read data from Oracle
- query multiple IIF statement
- SQLExecute() returns SQL_NEED_DATA
- Optimizing OLE DB Destination for Fast load from Oracle to SQL Server for SSIS
- Is there any better option to apply pagination without applying OFFSET in SQL Server?
- Multiple Security Predicates (FILTER|BLOCK) on same Table
- Untrusted Certificate Error when connecting to SQL Server running in Docker from .NET 7 API in VS2022
- Can using isnull in a where statement cause problems with using indexes?
- Parsing JSON nodes in the same string
- why is this subquery not working? SQL Server begginer practice case
- How to create a before delete trigger in SQL Server?
- sqlsrv array doesn't return all rows
- How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?
- SQL Stored Procedures not finishing when called from Python
- How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
- Rank rows by specified partition
- SQL Get "ISO Year" for ISO Week
- Trouble with sort order using Lead with window function
- Relational table design with same type in a many-to-many relationship
- Select 1 row from the dataset which satisfies a particular condition
- How to remove or alter the dbo prefix from SQL Server 2012 tables?
- Return Value of Previous Row Column
- Case expression for Order By clause with Desc/Asc sort
- Get lag value from the last row where value from other column = 'n'
- Date format in dd/MM/yyyy hh:mm:ss
- Get list of databases from SQL Server
- Pyodbc: Login Timeout Error
- Error when installing Microsoft Sql Server 2019, cannot find the microsoft OLE DB Driver
- How to create and use database directly after creation in SQL Server?
- Database Naming Conventions by Microsoft?
- How to compare values which may both be null in T-SQL