January 14, 2015 at 6:49 am
Hi Friends,
We have a SSIS package that is taking around 10 hrs loading around 43 million records which is taking around 14 GB data.
This package has around 30 Sql tasks, each task has a stored procedure that loads data for 10 tables from source. Source is DB2 for which we have linked server in SQL SERVER 2012. Totally it loads around 300 tables.
Its direct dump of tables without any transformations.
Is there a way to reduce the run time for this scenario.
Appreciate you help!
Thanks.
January 14, 2015 at 7:06 am
Do all the tasks run in parallel or serially?
If you are just exporting whole tables and then importing then into SQL Server, SSIS might not be the best tool to use. I'm not familiar with DB2, but is it possible to dump out tables to a file system which could then possibly be imported using BCP?
You could throw more resource at it - RAM, CPU.
Has it always taken this long or has it got worse over time?
Do you have indexes on your destination tables? If so you could try dropping the indexes, importing the data and then recreating the indexes.
Regards
Lempster
January 14, 2015 at 7:19 am
Thanks for the reply...for the queries:
its like Five tasks run parallelly under which we have other tasks.
The mappings all are there already, i need to try improve the performance.
Its always taking the same time. 9.30 to 10 hrs.
We do have indexes on destination tables, We need run the jobs very frequently.
January 14, 2015 at 7:22 am
Do not use a linked server but rather use a data flow where you directly connect to DB2.
Run as much as you can in parallel.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 7:23 am
sqlquery29 (1/14/2015)
Hi Friends,We have a SSIS package that is taking around 10 hrs loading around 43 million records which is taking around 14 GB data.
This package has around 30 Sql tasks, each task has a stored procedure that loads data for 10 tables from source. Source is DB2 for which we have linked server in SQL SERVER 2012. Totally it loads around 300 tables.
Its direct dump of tables without any transformations.
Is there a way to reduce the run time for this scenario.
Appreciate you help!
Thanks.
With that setup I don't think you can blame SSIS at all. You might be able to speed it up by running more tasks parallel, but that could also hit your resources hard.
I realize there are a lot of tables there, but especially if its a straight copy I'd consider doing a data flow for each table rather than a procedure across a linked server. It will be far easier to run in parallel and *probably* far faster. And if the design pattern is simple you can use BIML (there is a stairway for it here) to quickly generate the packages for each table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply