An Execute T-SQL task runs one or more SQL statements through a configured connection in the SSIS control flow. Mistake #1 is using it for every load or transformation: standard T-SQL can hide the pipeline logic, weaken SSIS error handling and logging, and make bad-row diagnosis harder. section 6.3 section 6.3.1
Choose the task deliberately rather than treating it as a replacement for every data flow:
- Prefer data flows when row-level error redirection, graphical transformation logic, pipeline inspection, or SSIS debugging matters. section 6.3
- Use native T-SQL for some large same-server loads when joining or merging performance and a narrow ETL window outweigh SSIS functionality. section 6.3
- Consider SQL Server Agent job steps when SSIS contributes little beyond SQL orchestration; SSIS can normally outperform linked servers for cross-server loads. section 6.3
- Connect tasks with success constraints to enforce sequential execution and prevent later statements from running after failure. section 6.3.1
A staged marketing load
Create an ADO connection manager, then run an INSERT from staging into marketing.Impressions, a MERGE into reporting.ImpressionAggregates, and a final TRUNCATE of staging.ImpressionsStage. SET DATEFORMAT DMY prevents date-overflow failures, while explicit casts and NULL handling address dirty source values. Keep the truncate task even when earlier loading tasks move to data flows. section 6.3.1
Listing 6.3 Loading data into the Impressions table
SET DATEFORMAT DMY
INSERT INTO marketing.Impressions (
ImpressionUID,
ReferralURL,
CookieID,
CampaignID,
RenderingID,
CountryCode,
StateID,
BrowserVersion,
OperatingSystemID,
BidPrice,
CostPerMille,
EventTime
)
SELECT
ImpressionUID
, ReferralURL
, CookieID
, CampaignID
, RenderingID
, CASE
WHEN CountryCode = 'NULL' THEN NULL
ELSE CountryCode
END CountryCode
, CASE
WHEN StateID = 'NULL' THEN NULL
ELSE StateID
END StateID
, BrowserVersion
, OperatingSystemID
, CAST(BidPrice AS MONEY)
, CAST(CostPerMille AS MONEY)
, EventTime
FROM staging.ImpressionsStage ;
Inspect the complete INSERT statement used to convert and load staged impression data. section 6.3.1
