SSIS

This page contains useful information when using the Integration Services in SQL Server 2005

---

Configuration Package

• SSIS > Package Configuration - Package Configuration Organizer
• Enable Package Configuration
• Add - Package Configuration Wizard
• Select configuration type eg. XML configuration file
• Add filepath and name.dtsconfig
• Select properties to Export
• Type in Configuration Name

---

Executing SSIS Package

In SQL Server Buisness Intelligence Developer Studio

• R> Package in SSIS Package Folder > Execute package > Debug > Stop debugging
• R> Package in SSIS Package Folder > SSIS Import and Export Wizard
• Execute Package Task (as Parent Package)

In SQL Server Management Studio

• R> Database > Tasks > Import OR > Export Data > SQL Server Import and Export Wizard
• Connect > Integration Services > Stored Packages > File System > Import Package. R> Package - Execute Package Utility
• In SQL Server Agent R> Jobs > New Job - Add Name. Steps > New > New Job Steps - Add Name. Select SQL Server Integration Services Package in Type drop down list. In Package Source - File System - Browse to Package

In DTExecUI

• Start > Run - dtexecui - Execute Package Utility
• In command line of Execute Package Utility save command as text (.bat or .cmd file). Exceute file

---

Logging SSIS Package

• SSIS > Logging
• Tick Package
• In Providers and logs tab select Provider type
• Add - select file in configuration field
• In details tab select event (s)

---

Debugging SSIS Package

• Add breakpoint to control flow: R> task or control flow > Edit Breakpoint - Set Breakpoint
• Add breakpoint to script task: F9
• Walk through script task: Continue (F5), Step Into (F11), Step Over (F10), Step out (Shift F11), Run to Cursor
• View Callstack, Locals, Output, Errors windows
• View Autos, Watch, Quickwatch, Immediate windows, DataTip in Script task
• Send to Output window: Debug.WriteLine()
• Progress messages displayed on Progress tab
• Disable individual tasks: R> Disable (task goes grey)
• Add Data Viewer to Data Flow Pipeline: R>Pipeline > add Grid, Histogram, Scatter Plot or Column Chart

---

Control Flow Containers

• Foreach Loop - control flow repeated number of times determined by enumerator
• For Loop - control flow repeated until expression = False (eg loop 5 times: initial expression @iCounter=0, evaluation expression @iCounter=5 and iteration expression @iCounter = @iCounter + 1)
• Sequence container - groups control flows
• Task Host - container for a single task

---

Control Flow

• Data Flow Task
• Script Task
• Execute SQL Task
• Execute Package Task

---

Data Flow

In SQL Server Buisness Intelligence Developer Studio

• Create new project and rename SSSI Package: R> rename

CREATE NEW DATA FLOW

• Drag control flow container onto control flow
• Drag Precedence Constraint between control flows if necessary (Evaluation:Constraint/Expression, AND/OR)
• Drag new data flow element into control flow container
• »data flow element

ADD SOURCE DATA ADAPTER TO DATA FLOW DESIGN VIEW

• Drag appropriate source from Data Flow Sources in Toolbox

CREATE CONNECTION TO SOURCE DATA ADAPTER
A: NEW CONNECTION

• »Connection Manager > New Connection > Create Connection

B: NEW DATA SOURCE

• R> Data Source - New Data Source - Data Source wizard
• »Connection Manager > New Connection From Data Source> Create Connection

C: NEW DATA SOURCE VIEW

• R> Data Source view folder - Data Source view wizard
• Data Source Design view R> New Named Query

ADD CONNECTION TO SOURCE DATA ADAPTER

• »Source Data Adapter
• > Select connection from Connection Manager Drop down list
• > Select Data Access mode
• > Select table or view if needed
• > Columns

ADD DESTINATION DATA ADAPTER TO DATA FLOW DESIGN VIEW

• Drag appropriate destination from Data Flow Destinations in Toolbox

ADD DATA FLOW TRANSFORMATION IF REQUIRED

CREATE DATA FLOW FROM SOURCE DATA ADAPTER

• Drag Green Arrow from Source Data Adapter to Destination Data Adapter
• »Green Line > Add Data Viewer

SELECT DESTINATION
A: NEW CONNECTION

• »Connection Manager > New Connection > Create Connection

B: DESTINATION DATA ADAPTER

• »Destination Data Adapter
• > Select File Connection manager - New
• > Columns

EXECUTE PACKAGE

• Finish and execute package

---

Data Flow Transformation

Row Transformations - updates or creates new columns

• Character Map - changes characters to different characters eg. lowercase
• Copy Column - creates a copy of a column
• Data Conversion - changes the data type of a column, creating a new column
• Derived Column - applies expression to column, creating a new derived column
• Script component - runs VB.Net script on rows in pipeline
• OLE DB Command - runs SQL statement on rows in pipeline

Rowset Transformations - creates or updates rows

• Aggregate - applies aggregate function to grouped records
• Sort - sorts column(s) and optionally remove rows with duplicate values in sort columns
• Percentage sampling - creates a random percentage sample from data in the pipeline
• Row sampling - creates a random row sample from data in the pipeline
• Pivot - creates a pivot dataset
• UnPivot - creates an anti-pivot dataset

Split and Join Transformations - distributes or joins rows to / from differing pipelines

• Conditional Split - fliters rows depending on condition
• Multicast - duplicates all rows in pipeline into 2 or more outputs
• Merge - merges 2 sorted datasets with same column structure into single output
• Merge Join - merges 2 datsets side by side using Full, Left or Inner join
• Union All - combines 2 or more datasets with same column structure into single output
• Lookup - joins input rows with columns in a reference dataset (table, view or SQL statement)

Data Quality Transformations

• Fuzzy Lookup - close or exact match between input row and columns in a reference dataset (SQL Server 2005 table)
• Fuzzy Grouping - close or exact match between input rows based on one or more columns

Data-Mining Transformations

• Data-Mining Query
• Term Extraction - extracts noun or noun phrases from input column
• Term Lookup - matches text in input column to a reference table

Other Transformations

• Export Column - exports data from a column into a file that is named from another column
• Import Column - inserts data from a file into a new column with file name given from another column
• Audit - adds system value eg.machine name, task id as a new column to dataset
• Row Count - counts the number of rows in data flow, storing value in a variable
• Slowly Changing Dimension

---

Script Component

• Drag Script Component onto Data Flow
• Select Script Component Type
• Drag green arrow from and to Source / Destination data flow tasks
• » Script Component - input columns, input and output (Add output/columns), Script > Design Script
• Input_ProcessInputRow (Row As InputofBuffer) method
• InputBuffer, OutputBuffer, Me.Variables, Me.ComponentMetadata (=Event/log)

---

Script Task

Class References

• R> References > Add Reference > Add component
• Add Imports CLASSNAME

VB.Net Script in ScriptMain Class

Progress Message

• Dts.Events.FireInformation (PARAMETERS)
• Dts.Events.FireProgress (PARAMETERS)
• Dts.Events.FireError (PARAMETERS) (In Catch)

Logging

• In script properties enable LoggingMode
• R> Control Flow > Logging
• Tick Package Name in Containers Panel
• Add and tick log in Providers and Log Panel
• >New Connection in Configuration field
• Select create file from Usage Type
• Browse for log file
• Tick script task in Containers tree view
• Tick SampleTaskLog in Details tab

• Dts.Log (PARAMETERS)

---

Variables

Set Variable

• R> Control Flow or Data Flow window > Variables
• Add Variable
• Give Name, Scope, Data Type, Value

Read/Write variable in Script Task

• »Script Task > Script - ReadOnlyVariable / ReadWriteVariable = VARIABLENAME
• Dts.Variables(VARIABLENAME).Value