Rough notes on SSIS

Some SSIS Components
====================

-File Copy task
- to move/copy files

-Audit Task
- for Package name etc.

-can create views using SQL script in SSIS

-can use SCD for lookups:
-but not cached + complex

-using a SP for lookups is not cached

-best use Lookup component for lookups, in combination with Cache Cxn Manager

SSIS Architecture:
==================

Types of component:
-asych component: has different input & output buffers (slower)

-synch component: same buffer for input & output (faster)
-> SynchOutputID is non-zero

Types of buffer:
-flat buffer <- Data Source -private buffer <- Sort, Aggregate, Lookup

SSIS Performance Notes
======================

-use OleDb not ADO (less layers)
-OleDb dest: set MaxInsertCommitSize = 10000
-use Select X WHERE Y - not a Table/View
-you can use a variable to create dynamic SQL

-source: flat file: "Fast Parse"

-remove unneeded columns, at source SELECT

-use SQL Server Destination

-synch components, not asynch (Sort, Aggregate)
-can sort using source SQL

-simplify VIEW SQL

-share lookup caches (avoids reloading data)

-consider avoiding OleDbCmd, as it is per-row

-DataFlow.EngineThreads - set to no. of DataFlowTassks (+ num source components)

-Stage data via Multicasts, so can process immediately

-remove unneeded columns, after async component

-Sort at Source (SQL)


Design
======

-limit synchronicity

-reduce staging + disk I/O

-reduce RDBMS (SQL)


Trouble Shoot Performance
=========================
-selectively replace / remove components: Dest / Src / Transform

-PerfMon + Pipeline Logging