Connection Manager in SSIS

Connection Manager is SSIS technology being used and a location of the data being used.

connection_manager

It has a type of data source (or OLE DB Provider), Authentication (which is Identity to be used to connect to this data Source) and initial catalog. This connection manager has Properties window,

detailed_properties_of_connection_manager

where ConnectionString is defined, DataSourceID, InitialCatalog and Authentication.

This connection manager is used to connect to several different providers and connection types. It can be OLE DB connection, ODBC connection, a flat file connection, ADO.NET connection, different Analysis services connection, or any other connection (DQS, FTP, HTTP, etc.) . By default connection manager in SSIS supports:

Connection_managers_types_SSIS

Also new connections can be added here, based on your local ODBC Data Source Administrator.

In SSIS there is also Project Connection manager available, which enables several different packages to use same data source connection. On the other hand you can have a package level connection manager, which will be used only for this particular package.

In terms of architecture, deployment and big picture, a parametrization of connection strings. This is new to SQL Server 2012 SSIS and deserves a special post.

Advertisements