A data flow is an SSIS pipeline for importing, exporting, and transforming rows in memory buffers. It sits inside a package’s control flow: the control flow orchestrates tasks, while a data flow moves rows along data paths between components. A package can contain zero or more data flows. section 6.0
Mistake #24: treating SSIS only as a T-SQL orchestration shell. Execute T-SQL tasks can load or transform tables, but replacing a pipeline with queries can hide ETL logic from later developers, make pipeline position harder to inspect, and turn one bad row into a failed query rather than a redirectable row. Data flows provide SSIS error handling, logging, and debugging support. section 6.3
Use the right tool for the load. Native T-SQL will likely outperform a data flow for joins and merges when source and destination are on one server, especially during a narrow ETL window. For cross-server loading, SSIS can normally outperform a linked server; where time permits, its logging, error handling, and debugging can outweigh raw speed. section 6.3
Staging impressions into a marketing table
A Load Marketing Impressions flow reads a staging table, uses a Derived Column transformation to produce correctly typed values, then inserts rows into marketing.Impressions. Expressions can add or replace columns and combine columns, functions, operators, and literals. The example wraps GUID strings in braces before casting to DT_GUID, normalizes slashes in EventTime before casting to DB_TIMESTAMP, and can expose expression parse errors by hovering over a red expression. section 6.3.2
Mistake #25: accepting the source’s default whole-table extraction when only a subset is needed. Unneeded columns make buffers larger, and rows that cannot affect the result consume more buffers. Project required columns and filter unchanged data at the source before rows enter the pipeline. section 6.4
Listing 6.7 Loading impression data
SELECT CampaignID, CountryCode, BidPrice, CostPerMille, EventTime FROM marketing.Impressions WHERE (EventTime >= GETDATE() - 30) ;
This source query selects five needed impression columns and limits rows to the last 30 days, illustrating source-side projection and filtering. section 6.4
Apply these rules when designing a data flow:
- Keep task sequencing and success, failure, or completion behavior in the control flow; use data paths to pass successful rows between data-flow components. section 6.0
- Choose native T-SQL for appropriate same-server, performance-critical work rather than adding SSIS overhead without benefit. section 6.3
- Read only columns and rows that the downstream transformation actually needs. section 6.4
