How to make the process quicker?

Question

We build a system that handling the billing analyse and what it does it just have a raw bill from vendor and process to "common" table and this is using SQL Server 2005 Integration Services.

The process as follow:

The Integration process is running locally on the same server. The process is reading the file and copy the exact data into staging table and then pick and choose certain criteria and dump into "common" table.

At the moment, the batch has 300,000 rows and it took about 6 minutes to process. Now, the current spec are: 2 CPUs Intel Xeon E5345 @ 2.33 GHz RAM 4GB

Is there anyway I can speed up this process?

Answer 1

There are some basic best practices for SSIS.

http://blogs.msdn.com/ashvinis/archive/2005/09/27/474563.aspx

http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

http://www.mssqltips.com/tip.asp?tip=1867