Home » SSIS Connection Managers

SSIS Connection Managers

SSIS connection managers are used to configure a connection between source and destination data stores such as Excel, Text, XML, FTP, Relational databases and so on.. to extract and load data.



SSIS uses the connection manager as a logical representation of a connection. At design time, you set the properties of a connection manager to describe the physical connection that Integration Services creates when the package runs.

Built-in Connection Managers

SQL Server Integration Services provides different types of built-in Connection Managers, following are the lists of connection manager types that SQL Server Integration Services provides.

Connection Manger Description
ADO Connects to ActiveX Data Objects (ADO) objects.
ADO.NET Connects to a data source by using a .NET provider.
CACHE Reads data from the data flow or from a cache file (.caw), and can save data to the cache file.
DQS connects to a Data Quality Services server and a Data Quality Services database on the server.
EXCEL Connects to an Excel workbook file.
FILE Connects to a file or a folder.
FLATFILE Connect to data in a single flat file.
FTP Connect to an FTP server.
HTTP Connects to a webserver.
MSMQ Connects to a message queue.
MSOLAP100 Connects to an instance of SQL Server Analysis Services or an Analysis Services project.
MULTIFILE Connects to multiple files and folders.
MULTIFLATFILE Connects to multiple data files and folders.
OLEDB Connects to a data source by using an OLE DB provider.
ODBC Connects to a data source by using ODBC.
SMOServer Connects to a SQL Server Management Objects (SMO) server.
SMTP Connects to an SMTP mail server.
SQLMOBILE Connects to a SQL Server Compact database.
WMI Connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.

A connection manager can be created at the Package level or at the Project level.

When a connection manager is created at the project level, it will be accessible for all the packages in the project whereas connection manager that is created at the package level will be accessible to that specific package only.

Project Level Connection Manager in SSIS

When you configure the Project Level Connection Manager, then the connection is accessible to all the packages under that project, so that you can use same connection in multiple packages.



Lets see how to create a project Level connection manager in SSIS.

Lets create a new SSIS project as shown below.

Now under the Solution Explorer, right click on Connection Mangers folder and select New Connection Manager as shown below.

Once you click New Connection Manager, a Add SSIS connection Manger window opens.

Now you can select any connection manager as per you requirement.

Lets select OLEDB connection manager, then click on ADD.. button.

After that, a Configure OLEDB connection Manager window opens.

Here you will see an existing connections if you have already created connection before else click on New.. button to create new connections.

Now select the Server Name, Authentication mode then select Database Name.

After that you can also verify connection by clicking on Test connection.

Next, click on OK button.





Once you click on OK button, you will see a Connection Manager that you created is displayed in Data Connections 

Now click on OK button, you can see a connection manager is created under folder Connection Managers.

Also you can see same connection manager in the bottom of SSIS designer in connection managers area.

Package Level Connection Manager in SSIS

When you create a package level connection manager, it will be accessible to that specific package only. It is always good to create a package level connection manager if you need that connection only for that package and do not want to use it in another package.

Lets see how to create a Package Level connection Manger.

Go the Connections Manager area that is at the bottom of SSIS designer.

Right click in Connections manager area, or window then you will see some favourite connections in context menu, or you can select new connections..

Lets select New Connection..



Once you click on New Connection…, you will see Add SSIS Connection Manager window opens, this is the same window that we have already been seen above.

Now you can follow the same steps configure the connection manager.

Once you done with this, you will see a package level connection managers is created that you can see in connections manager area as shown below.

Note that,  Project level connection managers is shown inside Connection Mangers folder under Solution Explorer pane also can be seen at the bottom of SSIS designer Connection Managers area, while Package level connection managers can be seen at the bottom of SSIS designer Connection Managers area only.

You can also refer microsoft documentation for more details : Integration services connections

Also Read…

Create a New SSIS Project

Load flat file data into SQL Server Table

Load Excel file data into SQL Server Table

Derived Column Transformation




 244 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.