Basic SSIS Notes

Setup
SSIS Basics
Script component
Control flow taks and precedence constraints
Visual walkthrough

Setup

Install Visual Studio 2019
- Ensure Data Tools component is included
- Install SQL Server Integration Services Projects visual studio extension
or Install Visual Studio 2022
- Ensure Data Tools component is included
- Install SQL Server Integration Services Projects 2022

SSIS Basics

Create a new project Integration Services Project.
Add a data flow task to the control flow.
Drag on sources from Other Sources or use the Source Assistant.
- Setup source connection manager
Drag on any common operations from the SSIS toolbox that is needed such as Script Components or other options.
Drag on destination from Other Destinations or use the Destination Assistant.
- Setup destination connection manager
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.