SQL Server Integration Services (SSIS) is Microsoft's ETL platform for building data-movement and transformation pipelines through a drag-and-drop graphical interface. It ships with SQL Server Enterprise and Standard editions, although Standard has feature limitations. section 6.0
An SSIS package contains one control flow that orchestrates tasks. Tasks can contain data flows, which import, export, and transform data in memory buffers. Precedence constraints determine whether tasks run after success, failure, or completion. section 6.0
A practical SSIS design separates orchestration from data movement:
- Use the control flow to sequence tasks rather than assuming every task runs in parallel. section 6.0
- Use data flows for importing, exporting, and transforming rows within memory buffers. section 6.0
- Choose success, failure, or completion constraints to express execution order and custom error-handling logic. section 6.0
Reporting pipeline
A reporting application can use SSIS to pull data from inventory and manufacturing applications, transform it, and load a denormalized structure optimized for reporting. section 1.2
Listing 6.1 Creating the Marketing database
CREATE DATABASE Marketing ; GO USE Marketing ; GO CREATE SCHEMA staging ; GO CREATE SCHEMA marketing ; GO CREATE SCHEMA reporting ; GO CREATE TABLE staging.ImpressionsStage ( ImpressionUID VARCHAR(MAX) NULL, ReferralURL VARCHAR(MAX) NULL, CookieID VARCHAR(MAX) NULL, CampaignID VARCHAR(MAX) NULL, RenderingID VARCHAR(MAX) NULL, CountryCode VARCHAR(MAX) NULL, StateID VARCHAR(MAX) NULL, BrowserVersion VARCHAR(MAX) NULL, OperatingSystemID VARCHAR(MAX) NULL, CostPerMille VARCHAR(MAX) NULL, EventTime VARCHAR(MAX) NULL, BidPrice VARCHAR(MAX) NULL ) ; CREATE TABLE marketing.Impressions ( ImpressionID BIGINT NOT NULL PRIMARY KEY IDENTITY, ImpressionUID UNIQUEIDENTIFIER NOT NULL, ReferralURL VARCHAR(512) NOT NULL, CookieID UNIQUEIDENTIFIER NOT NULL, CampaignID BIGINT NOT NULL, RenderingID BIGINT NOT NULL, CountryCode TINYINT NULL, StateID TINYINT NULL, BrowserVersion BIGINT NOT NULL, OperatingSystemID BIGINT NOT NULL, BidPrice MONEY NOT NULL, CostPerMille MONEY NOT NULL, EventTime DATETIME NOT NULL ) ; CREATE TABLE reporting.ImpressionAggregates ( ImpressionAggregateID BIGINT NOT NULL PRIMARY KEY IDENTITY, CampaignID BIGINT NOT NULL, CountryCode TINYINT NOT NULL, EventDate DATE NOT NULL, AvgBidPrice MONEY NOT NULL, AvgCostPerMille MONEY NOT NULL ) ;
The Marketing database definition supplies staging, marketing, and reporting schemas for demonstrating an SSIS data pipeline. section 6.0
