Types of connections in SSIS

Today we will discuss about various connections available in SSIS. First of all we will understand what connection is.

A connection is a communication between resources by which they can communicate with each other. In SSIS you can perform different tasks with connection such as

• Connect to relational data base to execute SQL commands or SQL stored procedures
• Connect to Excel sheet and fetch data
• Connect to flat file to import data
• Connect to XML file to extract data from nodes.

Connection manager is logical representation of connection which is placed under Design editor as shown in Figure 1. The properties, which are used to create connection at design time, will be used at runtime to establish physical connection to execute the SSIS package.

Figure 1

To add new connection, Right click on connection manager and select new connection which takes us to ADD SSIS connection Manager Tab as shown in Figure 2 this tab contains all built-in connections.

Figure 2

ADO connection: ADO connection allows package to connect to ActiveX data objects(ADO) such as record set. This connection manager is typically used in custom tasks written in an earlier version of language.

ADO.NET connection: ADO.NET connection enables package to connect data sources by using .NET provider. 

CACHE connection: Cache connection enables package to read data from cache file(.caw) and write data into cache file. The data is always saved in memory whether you can configure the cache connection manager.

EXCEL Connection: Excel connection allows package to connect existing excel work sheet to read data or write data.

FILE Connection: File connection allows package to connect existing file or folder or create file or folder during runtime. File can be may be either XML or excel file.

FLATFILE Connection: Flatfile connection allows package to connect to single flat file to access data.

FTP Connection: FTP(File Transfer protocol) connection allows package to connect FTP server to move or delete local and remote files and create or delete local and remote directories.

HTTP Connection: HTTP connection allows package to connect web server for receiving or sending files. Web service task uses HTTP connection to connection web service to execute web methods.

MSMQ Connection: MSMQ connection allows package to connect to message queue that uses Message queuing(MSMQ). Message queue task in SSIS this connection.

MSOLAP100: MSOLAP100 is a analysis service connection manager. This connection manager allows package to connect to Analysis database server or analysis services project to access cube and dimension data.

MULTIFILE Connection: MultipleFile connection allows package to connect existing multiple files or folders or create multiple files or folders during runtime. File can be may be either XML or excel file

MULTIFLATFILE Connection: multiple flat file connection allows package to connect multiple flat file to access data. In SSIS you can use multiflatfile connection to connect data from different sources.

ODBC Connection: ODBC connection allows package to connect to open databases using open data base connectivity(ODBC) specification.

OLEDB Connection: OLEDB connection allows package to connect to data source by using OLEDB provider. An OLEDB connection manger that connects to SQL server can use Microsoft OLEDB provider for SQL server.

SMOServer connection: This connection allows package to connect SQL management object(SMO) server to perform SQL server managing tasks.

SMTP Connection: This connection allows package to connect SMTP(Simple Mail Transfer protocol) server to send mails. Send mail task is SSIS uses the connection

SQLMOBILE Connection: This connection allows package to connect SQL server compact database(.sdf)

WMI Connection: This connection allows package to use WMI(Windows management instrumentation) to connect server or work station by writing WQL queries. WMI data reader task and WMI event watcher tasks is SSIS uses WMI connection manager.

All the above mentioned connections will be created in SSIS as and when we are creating respective task.

Please leave comment if you like the post