A data flow source reads rows from an external table into an SSIS data flow. The tempting default is to select a table and extract everything, but unnecessary columns and rows enlarge buffers and can slow the package. section 6.4
Mistake: extracting the whole impressions table
The Merge Aggregates flow needed only CampaignID, CountryCode, EventTime, BidPrice, and CostPerMille. Reading the entire marketing.Impressions table took 16.403 seconds; selecting those columns and filtering to the last 30 days reduced execution to 2.937 seconds. Configure the source with a SQL command when the flow needs only a subset. section 6.4
Use a data flow source safely and efficiently:
- Extract the whole table only when the flow genuinely needs every row and column. section 6.4
- Use a SQL command to select required columns and filter rows at the source. section 6.5
- Prefer OLE DB sources where possible, then test configurations against the performance needs of the environment. section 6.5
Listing 6.7 Loading impression data
SELECT CampaignID, CountryCode, BidPrice, CostPerMille, EventTime FROM marketing.Impressions WHERE (EventTime >= GETDATE() - 30) ;
Inspect the command that selects five columns from marketing.Impressions and restricts EventTime to the last 30 days. section 6.4
