topic Re: Processing large volumes from Oracle to SQLServer in Talend Studio

Processing large volumes from Oracle to SQLServer

DL001 — Thu, 06 Oct 2016 21:00:13 GMT

Hi
I'm involved in a project to move data from Oracle to SQLServer with some transformation and matching en route. I am currently evaluating Talend's DI product and coming from and SSIS background, I have some reservations about using compiled java components to carry out the migration.
The volume of data is in the region of 1.5-4 billion and I am trying to gain some confidence that Talend is up to the job.
Has anyone got some insights on processing such volumes with Talend or indeed some gotchas, pitfalls and advice? All comments welcome, thanks.

Re: Processing large volumes from Oracle to SQLServer

Anonymous — Fri, 07 Oct 2016 06:33:10 GMT

We have in past used talend to perform such huge migrations and have achieved decent performance. I will though advice following points:
- Any transformations that you want to do, please do them at source database itself
- Post transformation completion, use MSSQLBulk Load component to load your data over to the SQL server
- Enable Parallel loading on the SQL server end and perform bulk loading by disabling indexes on the target SQL table
https://technet.microsoft.com/en-us/library/ms186341(v=sql.105).aspx
- I will not advice using talend for transformations for such huge volume of data. I will advise doing transformations on the source end or target end (ELT in place of ETL)
Hope it helps.

Re: Processing large volumes from Oracle to SQLServer

Anonymous — Fri, 07 Oct 2016 10:05:05 GMT

Sankalp makes some very good points. The key one being that you must use all of the technologies involved in the right combinations. I have clients who insist on only using a single tool for a job. They claim this is done for ease of handover between developers. This is similar to using a lorry on a racetrack instead of using the lorry to bring the race car. Regardless of the tools, there will be overlap. In those situations you use the best tool for the job and ensure that you use developers who are skilled in those areas (....your granny cannot do data integration no matter how much certain vendor sales guys would like you to believe that). So yes, do as much of your transformation inside the database where it is possible. Do as much of your filtering inside the database where it is possible. You can write this code inside Talend (SQL queries are just Java Strings....this actually allows a lot of dynamic functionality).

Also, do not be worried about Java. Talend using Java is actually an advantage given all of the APIs there are today. There is nothing you cannot do with Talend and third party APIs. Performance can be a problem if you design your jobs badly, but then that is the case with any DI tool.