SSIS : Connection Managers

Connection managers represent a layer of abstraction between the SSIS package runtime environment and the data sources. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager. In fact, multiple tasks can use the same connection to the database. On the other hand, you could also create a separate connection manager to force each task to use a dedicated connection. You can define one or multiple data sources within your SSIS project. You could create connection managers without any dependency to the data source. However, if you define a project level data source, you can re-use the same source for multiple packages within your project ("project" and "solution" terms can be used interchangeably within BIDS). After you define a data source within your project, you can also create a data source view. A data source view is a layer of abstraction between the relational database and the row sets that you can make available for your transformations. For example, you can define a named query (similar to a relational database view) to join multiple tables, filter the output with the WHERE clause or define new columns based on the existing columns. Similarly, you could add named calculations to your tables, combining the first, middle and last names into a single column called "full_name". You are also allowed to add a friendly name to the tables, so that instead of calling a table "AdventureWorks.dbo.warehouse_sales" you can simply reference it as "sales". Why couldn't you define the similar views and aliases in the relational database? Well, sometimes you could have access to the relational database through an OLEDB driver, but you might NOT have permissions to create and alter database objects. This is when data source views are particularly helpful.

List of Connection Managers

S No	Name	Short Description
1	ADO	Used to connect to ActiveX Data Objects (ADO) objects, like a recordset.
2	ADO.NET	Used to access data sources by using a .NET provider and Microsoft SQL Server and XML
3	Cache	Used to read data from the cached server or from a cache file(.caw) so that the data is stored in the memory.
4	Excel	Used to connect to an existing Microsoft Excel workbook file for both Source and Destination processing of package
5	File	Used to connect to an existing file or to create a new file and use as a source or a destination.
6	FlatFile	Used to connect to a Flat file which acts like a source or a destination for the package to access and process the data across the platform.
7	FTP	Used to connect to a File Transfer Protocol Server to fetch the data or to update the data to the server.
8	HTTP	Used to access the webserver using the Hyper text transfer protocol to send and receive files across the servers
9	MSMQ	Used to connect to the Microsoft Message queuing server to access the messages as a source or to update the message as a destination
10	MultiFile	Used to reference to the existing file or folders or to create a new file and use it as a reference at runtime.
11	MultiFlatFile	Used to access the file using the flat file as multiple data source like using inside a loop container to loop through the file and access the data
12	OLEDB	Used to connect to the different data source using the OLEDB provider specifically used to connect to Microsoft SQL Server.
13	ODBC	Used to connect to different relational database system using the open connectivity provider
14	SMOServer	Used to connect to a SQL Management server objects to access for as a source or to update as a destination
15	SMTP	Used to connect to a Simple Mail transfer Protocol server to access and send mail or to receive mails
16	SQL Compact	Used to connect to SQL Server Compact database for light weight accessing of the server.
17	WMI	Used to connect to the Windows Management Instrumentation (WMI) in order to connect to the enterprise server for management.