Sunday 3 February 2013

SSIS Connection Manager

Introduction

After the proper understanding the BIDS it's time to understand the connection manager of SSIS package.  In this article I am trying to cover the connection manager and the purpose of it.

The Connection Manager

The Connection manager dialog box helps us to select the type of connection to add to a package. The connection contains connection string that point to files or databases.

Available connection manager

SSIS uses connection managers to integrate different data sources into packages. SSIS includes a wide variety of different connection managers that allow us to move data around from place to place.

The details are mentioned bellow.

Connection Manager
Handles
ADO
Connecting to ADO objects such as a Recordset.
ADO.NET
Connecting to data sources through an ADO.NET provider.
CACHE
Connects to a cache either in memory or in a file
MSOLAP100
Connecting to an Analysis Services database or cube.
EXCEL
Connecting to an Excel worksheet.
FILE
Connecting to a file or folder.
FLATFILE
Connecting to delimited or fixed width flat files.
FTP
Connecting to an FTP data source.
HTTP
Connecting to an HTTP data source.
MSMQ
Connecting to a Microsoft Message Queue.
MULTIFILE
Connecting to a set of files, such as all text files on a particular hard drive.
MULTIFLATFILE
Connecting to a set of flat files.
ODBC
Connecting to an ODBC data source.
OLEDB
Connecting to an OLE DB data source.
SMOSever
Connecting to a server via SMO.
SMTP
Connecting to a Simple Mail Transfer Protocol server.
SQLMobile
Connecting to a SQL Server Mobile database.
WMI
Connecting to Windows Management Instrumentation data.


How to use Connection Manager

To create a connection manager just right click it and choose one from the shortcut menu.



Here I choose New Connection… The Connection manager dialog box appears.



From depending on types, we can choose the connection.

Where It Used

The connection manager can be used at data flow source and destination.
Here some pictorial diagram showing that the connection manager is using at Data Flow sources.




Related tropics

Understand the BIDS




Hope you like it.



Posted by: MR. JOYDEEP DAS

1 comment:

  1. Very Nice Article,

    Connection Manager and their uses are described clearly.Its really great.

    I have also got a relevant article called SSIS Excel File Destination which is used to write data to Excel file from any source (e.g. SQL Server, Oracle, MySQL).

    ReplyDelete