Basic SSIS Notes

Setup

  1. Install Visual Studio 2019
    • Ensure Data Tools component is included
  2. Install SQL Server Integration Services Projects visual studio extension

SSIS Basics

  1. Create a new project Integration Services Project.
  2. Add a data flow task to the control flow.
  3. Drag on sources from Other Sources or use the Source Assistant.
    • Setup source connection manager
  4. Drag on any common operations from the SSIS toolbox that is needed such as Script Components or other options.
  5. Drag on destination from Other Destinations or use the Destination Assistant.
    • Setup destination connection manager
  6. Create Package Confirgurations such as xml so the package can be compiled and run with config options from an xml file or environment variable or other config source.
    • Visual studio menu -> Project -> Convert to package deployment model.
    • Visual studio menu -> Extensions -> SSIS -> Package Configurations.
    • Can at runtime specifiy settings for connection managers and other settings.

Note: If connecting to SQL server the package/visual studio must run as an admistrator. Why?

Script component

A script component is a powerful option if a custom tranformation needs to be written. With this a custom c# solution can be written as part of the transformation stage. This can be used when a sql option or pre built or third party option is not available.

Control flow taks and precedence constraints

Control can setup multiple tasks that can call other tasks. Sub tasks can be setup to run on success or falure.

Visual walkthrough

Control flows

Control Flows

Dataflows

Dataflows

Script components

Script components

Script components tranformation c# source editor

Script components tranformation c# source editor

Source edtor

Source editor

Desintation editor

Desintation editor

Package configuration

Package configuration

Package configuration type

Package configuration type

Package configuration properties

Package configuration properties

Package configuration xml

Package configuration xml

Parallel control flow tasks

Package configuration xml