SQL Server Integration Services: ETL, Reporting, and Analysis Services

| FullName | [FirstName] + " " + [LastName] | DT_WSTR(100) |
| FormattedPhone | REPLACE([Phone],"-","") | DT_WSTR(20) |
| LoadDate | GETDATE() | DT_DBTIMESTAMP |

Conditional Split Transformation:

Step 5: Error Handling

<!-- Configure error output on source -->
<OleDbSource ErrorRowDisposition="RedirectRow">
  <!-- Errors redirected to error log file -->
</OleDbSource>

Slowly Changing Dimensions (SCD)

SCD Type 1: Overwrite (No History)

-- Merge statement for Type 1
MERGE dbo.DimCustomer AS target
USING dbo.StagingCustomer AS source
ON target.CustomerID = source.CustomerID

WHEN MATCHED THEN
```sql
UPDATE SET
    target.CustomerName = source.CustomerName,
    target.Email = source.Email,
    target.ModifiedDate = GETDATE()

WHEN NOT MATCHED BY TARGET THEN

INSERT (CustomerID, CustomerName, Email, CreatedDate)
VALUES (source.CustomerID, source.CustomerName, source.Email, GETDATE());

**SCD Type 2: Preserve History (Versioning)**

```sql
-- Add version columns to dimension
ALTER TABLE dbo.DimCustomer ADD
```text
EffectiveDate DATETIME NOT NULL DEFAULT('1900-01-01'),
ExpirationDate DATETIME NULL,
IsCurrent BIT NOT NULL DEFAULT(1);

-- SCD Type 2 merge logic
-- 1. Expire current records where values changed
UPDATE target
SET

target.IsCurrent = 0,
target.ExpirationDate = GETDATE()```
FROM dbo.DimCustomer target
INNER JOIN dbo.StagingCustomer source ON target.CustomerID = source.CustomerID
WHERE 
```text
target.IsCurrent = 1
AND (target.CustomerName <> source.CustomerName OR target.Email <> source.Email);

-- 2. Insert new versions for changed records
INSERT INTO dbo.DimCustomer (CustomerID, CustomerName, Email, EffectiveDate, IsCurrent)
SELECT

source.CustomerID,
source.CustomerName,
source.Email,
GETDATE(),
1```
FROM dbo.StagingCustomer source
INNER JOIN dbo.DimCustomer target ON source.CustomerID = target.CustomerID
WHERE 
```text
target.IsCurrent = 0
AND target.ExpirationDate = GETDATE();

-- 3. Insert new records
INSERT INTO dbo.DimCustomer (CustomerID, CustomerName, Email, EffectiveDate, IsCurrent)
SELECT

CustomerID,
CustomerName,
Email,
GETDATE(),
1```
FROM dbo.StagingCustomer
WHERE CustomerID NOT IN (SELECT CustomerID FROM dbo.DimCustomer);


> **Architecture Overview:** **SSIS Slowly Changing Dimension Wizard:**

-- Track last successful load timestamp
CREATE TABLE dbo.ETLControl (
```text
PackageName NVARCHAR(100) PRIMARY KEY,
LastLoadDate DATETIME,
LastLoadStatus NVARCHAR(20)```
);

-- SSIS Package variable: @LastLoadDate
-- Set from ETLControl table in Execute SQL Task

-- Incremental extract query
SELECT 
```text
CustomerID,
CustomerName,
Email,
ModifiedDate```
FROM dbo.Customers
WHERE ModifiedDate > ?  -- Parameter from @LastLoadDate variable

-- Update ETLControl after successful load
UPDATE dbo.ETLControl
SET 
```text
LastLoadDate = GETDATE(),
LastLoadStatus = 'Success'```
WHERE PackageName = 'CustomerETL';

Package Parameters and Configurations

Project Parameters:

• Right-click project → Parameters
• Add: ServerName, DatabaseName, EmailRecipient

Use Parameter in Connection Manager:

• Right-click Connection Manager → Parameterize
• Property: ConnectionString
• Expression: "Data Source=" + @[$Project::ServerName] + ";Initial Catalog=" + @[$Project::DatabaseName]

Environment Variables (SSISDB Catalog):

-- Create environment
EXEC catalog.create_environment 
```text
@folder_name = 'ETL',
@environment_name = 'Production';

-- Add environment variables
EXEC catalog.create_environment_variable

@folder_name = 'ETL',
@environment_name = 'Production',
@variable_name = 'ServerName',
@data_type = 'String',
@value = 'PROD-SQL01';

-- Map to project parameter
EXEC catalog.create_environment_reference

@folder_name = 'ETL',
@project_name = 'CustomerETL',
@environment_name = 'Production',
@reference_id = @reference_id OUTPUT;

EXEC catalog.set_object_parameter_value

@folder_name = 'ETL',
@project_name = 'CustomerETL',
@parameter_name = 'ServerName',
@parameter_value = 'ServerName',
@object_type = 20,
@value_type = 'R';  -- R = Reference

## SSRS - Reporting Services

### Creating a Report

![SSRS - Reporting Services](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec1-chart.jpg)


**SQL Server Data Tools → New Report:**

1. **Data Source:**

```xml
<DataSource Name="AdventureWorks">
  <ConnectionString>
```text
Data Source=localhost;Initial Catalog=AdventureWorks```
  </ConnectionString>
  <UseSharedDataSource>true</UseSharedDataSource>
</DataSource>

2. Dataset:

SELECT 
```text
o.OrderID,
c.CustomerName,
o.OrderDate,
o.TotalAmount,
o.Status```
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE 
```text
(@StartDate IS NULL OR o.OrderDate >= @StartDate)
AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
AND (@Status IS NULL OR o.Status = @Status)```
ORDER BY o.OrderDate DESC;

3. Report Parameters:

Status Parameter - Available Values:

• Dataset query:

SELECT DISTINCT Status FROM dbo.Orders
UNION ALL
SELECT 'All' AS Status;

4. Report Layout:

Tablix (Table) Control:

Diagram: See the official Microsoft documentation for architecture details.

Expressions:

• Format currency: =Format(Fields!TotalAmount.Value, "C")
• Conditional formatting: =IIF(Fields!Status.Value = "Cancelled", "Red", "Black")

Matrix Report (Pivot Table)

-- Dataset for matrix
SELECT 
```text
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
Status,
SUM(TotalAmount) AS Revenue```
FROM dbo.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate), Status;

Matrix Layout:

Architecture Overview: Pending Completed Cancelled Row Total

Chart Reports

Add Chart Control:

• Toolbox → Chart → Drag to report
• Chart Type: Column, Bar, Line, Pie, Area

Configure Chart:

Category Groups: =Fields!OrderMonth.Value
Series Groups: =Fields!Status.Value
Values: =Sum(Fields!TotalAmount.Value)

Subscriptions

SQL Server Reporting Services Portal:

1. Navigate to report
2. Subscribe → Create subscription

Email Subscription:

• To: sales@contoso.com
• Subject: Monthly Sales Report - @ReportExecutionTime
• Render Format: PDF
• Schedule: Monthly (1st day, 8:00 AM)

Data-Driven Subscription (Enterprise Edition):

-- Create subscriber list
CREATE TABLE dbo.ReportSubscribers (
```text
SubscriberID INT PRIMARY KEY IDENTITY,
EmailAddress NVARCHAR(100),
CustomerID INT,
FileFormat NVARCHAR(20)```
);

-- Query for data-driven subscription
SELECT 
```text
EmailAddress AS TO,
'Customer Report' AS Subject,
FileFormat,
CustomerID  -- Passed to report parameter```
FROM dbo.ReportSubscribers;

SSAS - Analysis Services

Creating a Tabular Model

SQL Server Data Tools → New Project → Analysis Services Tabular Project

1. Import Data:

• Model → Import from Data Source
• Choose SQL Server
• Select tables: FactSales, DimCustomer, DimProduct, DimDate

2. Create Relationships:

Architecture Overview: FactSales[CustomerKey] → DimCustomer[CustomerKey]

3. Create Measures (DAX):

-- Total Sales
TotalSales := SUM(FactSales[SalesAmount])

-- Total Quantity
TotalQuantity := SUM(FactSales[Quantity])

-- Average Order Value
AvgOrderValue := DIVIDE([TotalSales], DISTINCTCOUNT(FactSales[OrderID]))

-- Sales YTD
SalesYTD := TOTALYTD([TotalSales], DimDate[Date])

-- Sales Previous Year
SalesPY := CALCULATE([TotalSales], SAMEPERIODLASTYEAR(DimDate[Date]))

-- Sales Growth %
SalesGrowth% := DIVIDE([TotalSales] - [SalesPY], [SalesPY], 0)

-- Top 10 Products
Top10ProductSales := 
CALCULATE(
```text
[TotalSales],
TOPN(10, ALL(DimProduct[ProductName]), [TotalSales], DESC)```
)


> **Architecture Overview:** **4. Create Hierarchies:**

-- Full Name
DimCustomer[FullName] = DimCustomer[FirstName] & " " & DimCustomer[LastName]

-- Sales Category
FactSales[SalesCategory] = 
```text
SWITCH(
    TRUE(),
    FactSales[SalesAmount] >= 1000, "High",
    FactSales[SalesAmount] >= 500, "Medium",
    "Low"
)

### MDX Queries (Multidimensional Model)

```mdx
-- Basic MDX query
SELECT 
```text
[Measures].[Total Sales] ON COLUMNS,
[DimDate].[Calendar].[Month].MEMBERS ON ROWS```
FROM [SalesCube];

-- Filter by year
SELECT 
```text
[Measures].[Total Sales] ON COLUMNS,
[DimProduct].[Category].[Category].MEMBERS ON ROWS```
FROM [SalesCube]
WHERE [DimDate].[Calendar].[Year].&[2025];

-- Top 10 products
SELECT 
```text
[Measures].[Total Sales] ON COLUMNS,
TOPCOUNT([DimProduct].[Product].[Product].MEMBERS, 10, [Measures].[Total Sales]) ON ROWS```
FROM [SalesCube];

-- Year-over-year growth
WITH MEMBER [Measures].[Sales Growth] AS
```text
([Measures].[Total Sales] - 
 ([Measures].[Total Sales], ParallelPeriod([DimDate].[Calendar].[Year], 1)))
/ ([Measures].[Total Sales], ParallelPeriod([DimDate].[Calendar].[Year], 1))
, FORMAT_STRING = "Percent"```
SELECT 
```text
{[Measures].[Total Sales], [Measures].[Sales Growth]} ON COLUMNS,
[DimDate].[Calendar].[Year].MEMBERS ON ROWS```
FROM [SalesCube];

Connecting to SSAS from Excel

Excel → Data → Get Data → From Database → From Analysis Services

1. Server: localhost\TABULAR
2. Database: SalesModel
3. Connect

PivotTable:

• Rows: DimProduct[Category]
• Columns: DimDate[Year]
• Values: TotalSales

Excel Formulas with SSAS:

=CUBEVALUE("SalesModel", "[Measures].[TotalSales]", "[DimDate].[Year].&[2025]")

=CUBEMEMBER("SalesModel", "[DimProduct].[Category].&[Electronics]")

Deployment

Deploy SSIS Package to SSISDB

# PowerShell deployment script
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[Reflection.Assembly]::LoadWithPartialName($SSISNamespace) | Out-Null

$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$integrationServices = New-Object $SSISNamespace.IntegrationServices $sqlConnection

## Create catalog if not exists
if (!$integrationServices.Catalogs["SSISDB"]) {
```powershell
$catalog = New-Object $SSISNamespace.Catalog ($integrationServices, "SSISDB", "P@ssw0rd123!")
$catalog.Create()```
}

![Create catalog if not exists](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec4-implementation.jpg)


$catalog = $integrationServices.Catalogs["SSISDB"]

## Create folder
$folder = New-Object $SSISNamespace.CatalogFolder ($catalog, "ETL", "ETL Projects")
$folder.Create()

![Create folder](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec5-implementation.jpg)


## Deploy project
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes("C:\Projects\CustomerETL\bin\Development\CustomerETL.ispac")
$folder.DeployProject("CustomerETL", $projectFile)

![Deploy project](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec6-pipeline.jpg)


Write-Host "Project deployed successfully"

Execute SSIS Package

Figure: SSIS package designer – data flow, transformations, and connections.

-- Execute package from SSISDB
DECLARE @execution_id BIGINT;

![Execute SSIS Package](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec7-pipeline.jpg)


EXEC catalog.create_execution 
```text
@folder_name = 'ETL',
@project_name = 'CustomerETL',
@package_name = 'LoadCustomers.dtsx',
@execution_id = @execution_id OUTPUT;

-- Set parameter values
EXEC catalog.set_execution_parameter_value

@execution_id,
@object_type = 50,  -- Package
@parameter_name = N'ServerName',
@parameter_value = 'PROD-SQL01';

-- Start execution
EXEC catalog.start_execution @execution_id;

-- Check execution status
SELECT

execution_id,
folder_name,
project_name,
package_name,
status,
start_time,
end_time```
FROM catalog.executions
WHERE execution_id = @execution_id;

SQL Server Agent Job for SSIS

-- Create job
EXEC msdb.dbo.sp_add_job
```text
@job_name = 'Daily Customer ETL',
@enabled = 1;

-- Add job step
EXEC msdb.dbo.sp_add_jobstep

@job_name = 'Daily Customer ETL',
@step_name = 'Execute SSIS Package',
@subsystem = 'SSIS',
@command = '/ISSERVER "\"\SSISDB\ETL\CustomerETL\LoadCustomers.dtsx\"" /SERVER "\"localhost\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@retry_attempts = 3,
@retry_interval = 5;

-- Add schedule (daily at 2 AM)
EXEC msdb.dbo.sp_add_schedule

@schedule_name = 'Daily at 2 AM',
@freq_type = 4,  -- Daily
@freq_interval = 1,
@active_start_time = 020000;

EXEC msdb.dbo.sp_attach_schedule

@job_name = 'Daily Customer ETL',
@schedule_name = 'Daily at 2 AM';

-- Add notification
EXEC msdb.dbo.sp_add_notification

@alert_name = 'Daily Customer ETL',
@operator_name = 'DBA',
@notification_method = 1;  -- Email

## Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

| Approach | Best For | Tradeoff |
|----------|----------|----------|
| Managed / platform service | Rapid delivery, reduced ops burden | Less customisation, potential vendor lock-in |
| Custom / self-hosted | Full control, advanced tuning | Higher operational overhead and cost |

> **Recommendation:** Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

## Validation and Versioning

- Last validated: April 2026
- Validate examples against your tenant, region, and SKU constraints before production rollout.
- Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

## Security and Governance Considerations

- Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
- Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
- Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

## Cost and Performance Notes

- Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
- Baseline performance with synthetic and real-user checks before and after major changes.
- Scale resources with measured thresholds and revisit sizing after usage pattern changes.

## Official Microsoft References

- https://learn.microsoft.com/sql/
- https://learn.microsoft.com/azure/azure-sql/
- https://learn.microsoft.com/fabric/database/

## Public Examples from Official Sources

- These examples are sourced from official public Microsoft documentation and sample repositories.
- Documentation examples: https://learn.microsoft.com/sql/
- Sample repositories: https://github.com/microsoft/sql-server-samples
- Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

## Key Takeaways

- SSIS provides robust ETL with visual designer and transformations
- Use Lookup transformation for reference data validation
- Implement SCD Type 2 for historical dimension tracking
- Design incremental loads with watermark timestamps
- SSRS supports parameterized reports with subscriptions
- Matrix reports enable pivot table-style analysis
- SSAS Tabular uses DAX for powerful business logic
- Deploy SSIS packages to SSISDB catalog for management
- Schedule ETL jobs with SQL Server Agent
- Monitor executions with catalog views

![Key Takeaways](/images/articles/sql/2025-06-09-sql-server-integration-services-etl-reporting-analysis-sec8-takeaway.jpg)


## Next Steps

- Build first SSIS package with error handling
- Create parameterized SSRS report with drill-down
- Design SSAS tabular model with DAX measures
- Automate deployments with PowerShell
- Implement logging and auditing for ETL processes


## Additional Resources

- [SSIS Documentation](https://learn.microsoft.com/sql/integration-services/sql-server-integration-services)
- [SSRS Documentation](https://learn.microsoft.com/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports)
- [SSAS Tabular](https://learn.microsoft.com/analysis-services/tabular-models/tabular-models-ssas)
- [DAX Reference](https://learn.microsoft.com/dax/dax-function-reference)


---

*Extract. Transform. Load. Analyze. Decide.*