SQL Server Integration Services (SSIS)

Package

A package is an assembled collection of

connections (See also DTSConn.dll)
control flow elements (of which at least one is required in a package and which consists of one or more tasks and containers which are executed when the package runs).
data flow elements (which consists of data sources and destinations)
event handlers (a workflow that is triggered by packages, tasks or containers when certain events occur)
variables
parameters
configurations (property-value pairs)

A package can be created with a graphical tool or programmatically.

The two graphical tools are

SSIS Designer
SQL Server Import and Export wizard

Packages are stored either

in SQL Server,
the SSIS Package Store or
in the file system

Multiple packages can be put into a project which then can be deployed as a unit (see .ispac files)

Packages can be configured and executed with DTExec.exe.

Designer

An installation of SSIS does not come with the tools with which SSIS packages can be designed and developed.

This is probably the reason why there is also SSIS Designer.

SSIS Designer is a graphical tool to create and maintain «SSIS packages». In particular, it allows to

construct a package's control flow
constract a package's data flows
adding event handlers to the package and the package objects
view the package content
view the execution progress at run time

SSIS Designer is availabe in SQL Server Data Tools (SSDT).

Catalog

The catalog database(?) stores

projects
packages
parameters
environments
operation history

The catalog can be installed with SSMS: Object explorer -> Integration Services Catalog -> right click -> Create catalog …

This creates a database named SSISDB.

Data protection: Some sensitive data stored in the catalog is encrypted for protection.

Azure Feature pack

The SSIS Feature Pack for Azure is an extension that provides the functionality to

connect to Azure services
transfer data between Azure and on-prem databases
process data stored in Azure

Connection managers are available for

Azure Data Lake Analytics
Azure Data Lake Store
Azure HDInsight
Azure Resource manager
Azure Storage Connection
Azure Subscription

Data Flow components:

Azure Blob Source/Destination
Azure Data Lake Store Source/Destination
Flexible File Source/Destination

Task include

Azure Blob Download/Upload
Azure Data Lake Analytics
Azure HDInsight Create/Delete Cluster
Azure HDInsight Hive/Pig
Azure Snynapse Analtics
Flexible File

Scale Out

SSIS Scale Out allows to distribute the work performed by a package accross multiple servers.

In an SSIS Scale Out environment, there is one Scale Out Master and ane or more Scale Out Workers.

.NET Assemblies

.NET assemblies that can be used for programming integration services are:

`Microsoft.SqlServer.ManagedDTS.dll`	Managed run-time engine.
`Microsoft.SqlServer.RuntimeWrapper.dll`	Primary interop assembly (PIA) (i. e. the wrapper) for the native run-time engine.
`Microsoft.SqlServer.PipelineHost.dll`	Data flow engine (managed)
`Microsoft.SqlServer.PipelineWrapper.dll`	Primary interop assembly (again: wrapper) for the native data flow engine.

TODO

Microsoft Connectors v4.0 for Oracle and Teradata is written to achieve optimal performance when loading data into Oracle or unloading data from Oracle in the context of Microsoft SSIS.

SSISBuild.exe builds SSIS projects in project deployment model or package deployment model.

SSISDeploy.exe deploys .ispac files to the SSIS catalog or .dtsx files and their dependencies to the file system.

DTUtil.exe

As per a comment of this Stackoverflow answer, I had to download this 400mb extension and install it in order to be able to create an Integration Services Project in Visual Studio.

This Youtube movie, which I found later, indicates that its also possible to install integration services for Visual Studio from Visual Studio via its «marketplace».

%DevEnvDir%\CommonExtensions\Microsoft\SSIS\nnn\Binn

The SSIS Toolbox can be shown (again) with View -> Other Windows -> SSIS Toolbox.

32-Bit and 64-Bit Providers for Connection Managers

The Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel class.