This article is talking about the optimization of the SSIS, I've attended three sessions for Donald Farmer, Principal Program Manager for Microsoft SQL Server Integration Services in Microsoft Redmond,Seattle in the MDC 2007 for the Middle east developers which was held in Cairo International Conference Center; the three sessions were :
 
    • High Performance Data Integration with SQL Server Integration Services
    • Using Analysis Services and Data Mining to understand your customers
    • Reporting Services, Report Builder and Sharepoint

I'll talk in here about the first session which was from my point of view the most intresting, First he described the difference between the DTS and the SQL Server Integration Services and it's Huge from my experience in this field and then He described the OVAL Concept which He meant with it the following:

O --> Operations

V --> Volume

A --> Application

L --> Location

Let's discuss each and everyone of these factors:

Operations: All the operations that happens during the data transfer whether it was defined or hidden done by the system.

Volume: The Volume of the data that will be transfered, it differs whether i'm movind a large amount of data ( Millions of rows ) or small amount (hundreds) in the time it takes to be moved.

Application: We have to take care which application we will use to move the data , if we are moving a bulk amount of data without doing any operations , we can use BCP instead of SQL Server Integration Services , becasue it will saves us time.

Location:  We have to the Source and Destination , the Type of network between them and the type of the Harddisk we are using in both places , cause may be when i change the hardware we save time and this for many company will be very benificial.

so the conclusion is that we have to take care of these four factors when we design a SSIS package.

The second thing that i want to talk about is How you detect the time leakage in your Package and How can you optimize it?

Donald Farmer has described this nice method of calculating the time of execution in the package and how can we detect the leakage;

first we run the package as it is and we see the execution time it takes let assume that it took 29 sec

then the STD execution time was 29 sec

Secondly we replace the destination with the Row Count component which is very useful component in here; it just count rows and we see the execution time of the package without the destination let's assume its gonna be 20 sec then STC took 20 sec to caluculate the time the data took to be transfered to the destination D = STD - STC= 29-20 =9 sec
To calculate the source time, you will Replace the destinaiton and the operations with the Row Count Component and execute the package and let's caluclate the execution time, let's assume it will be 15 sec; so we know now that the source took about 15 sec then by small mathematical operation we will know that the Operations took T = STC - SC= 20-15 = 5 sec

To Optimize this Integaration Package let's analyze the situation , I guess that the operations doesn't take much time and the destination too, may be If we investigated more we will find that the problem issued from the Old Hard disk we use in the source which tooks us too much time to read the data , so it won't matter how much time and money we invest to develop a new package the problem will still in the old harddisks so if we replaced them with a new one the performance will be enhanced significantly.

This was the end of the session I hope you like it as I did.

Thank you Donald.