(Integration Services) Stress Test 3: Lookups & Filters

Another analysis to do is measure the time taken for the transformation of "lookup" and "filter" with the same file that the previous tests.

In this test (with SSIS), the first run was enough to document and compare because the timing was fairly good, without having to modify the design and SETTINGS to reduce the execution time.

The consideration to keep in mind here when doing the job is to save the records in memory (cache), "Lookup - Cache Full", as does datastage when saving data in a hash. The worst option of all would be join the flows, which would require sorting. Other way is to use the Cache Transformation.

- Negative point in this test is the problems between OLEDB / MySQL and SSIS: constant errors are generated from its interaction


Objective: To measure elapsed time reading 6 million rows, from Flat file, join the main flow with a lookup table (MySql) and take attributes. Filter the flow and write a txt file.
Rows: 6.024.000 M
Columns: 37 Columns
Resources: Virtual machine with: 2 GB RAM, SSIS like main process over the virtual plataform. The resources used are anecdotal, today, Any production environment has enough processing power for current and future requirements. The objective here, is to build, to execute and to measure with the same environment (regardless of the limited resources)

Design & Run



Elapsed time (s)

77 sec.

Summary of log