- Install Visual Studio 2019
- Ensure Data Tools component is included
- Install SQL Server Integration Services Projects visual studio extension
- 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?
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.
Script components tranformation c# source editor
Package configuration type
Package configuration properties
Package configuration xml
Parallel control flow tasks