SSIS newbie

View previous topic View next topic Go down

SSIS newbie

Post  TheNJDevil on Mon Apr 30, 2012 12:33 pm

Obviously I am new to using SSIS. I have a process that seems fairly typical overall. The process needs to pull data from one SQL Server, to another SQL Server, cleanse the data, then put that into the proper dimensions and fact table. It's only about 35,000 rows of data right now. I have written a few stored procedures that does what SSIS does (the whole ETL process).

Executing the SSIS package from the DW server, it takes 2 minutes 3 seconds on average. Executing my stored procedures (well the one that calls the others), takes only 18 seconds. Even though SSIS can process multiple branches in parallel, it is 10 times slower than my hand coded solution.

The users want this run every 15 minutes during working hours. Is there any tips for how to get this to process faster? Any other suggestions.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: SSIS newbie

Post  Mike Honey on Mon Apr 30, 2012 7:54 pm

Hi TheNJDevil,

I suggest you check that your Data Flow Destinations have the Data access mode set to: Table or view - fast load, Table lock checked and Rows per batch & Maximum insert commit size both set to 100000.

With that change, I'd expect it to run faster than a stored procedure solution.

Good luck!
Mike


Last edited by Mike Honey on Mon Apr 30, 2012 7:55 pm; edited 1 time in total (Reason for editing : typo)
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: SSIS newbie

Post  TheNJDevil on Wed May 02, 2012 11:58 am

The Default Buffer Size change is what really cut the time down. The stored procedures version takes anywhere from 10 - 23 seconds depending on network usage. The SSIS package now processes in a similar range, but without Audit records being produced. I will get those added.

Thank you very much Mike for pointing me in the right direction.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: SSIS newbie

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum