SSIS newbie

[TheNJDevil]

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.

[Mike Honey]

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

[TheNJDevil]

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.

[Sponsored content]