SSIS Connection Manager

As we all know Microsoft SQL Server Integration Services packages are used to transfer data from different sources to different destinations such as files, Excel Worksheets, SQL Database, Oracle Database and so on. In order to extract the data or to insert data we need appropriate SSIS Connection Manager.

The SSIS Connection Manager include Connection Strings which is the combination of Server instance Name, Database Name, provider Name and credentials.

Built-in SSIS Connection Manager

SQL Server Integration Services provides different types of built-in Connection Managers. Using these Connection Managers, SSIS packages will connection to different types of Data Sources such as Oracle, Access, Excel, Text File, SQL server, SQL Server Analysis Services and so on. Below table will show you the built-in SSIS Connection Managers

Connection Manager	Description
ADO	This Connection Manager is used to connect with ActiveX Data Objects or we can simply called as ADO object. Please refer ADO Connection Manager in SSIS article to understand the configuration settings.
ADO.NET	This is used to connect to a data source by using a .NET provider. Please refer ADO.NET Connection Manager in SSIS article to understand the configuration settings.
CACHE	Reads data from the data flow or from a cache file (.caw), and can save data to the cache file. Please refer Cache Connection Manager article to understand the configuration settings.
DQS	Connects to a Data Quality Services server and a Data Quality Services database on the server.
EXCEL	Connects to an Excel workbooks file. Please refer Excel Connection Manager in SSIS article to understand the configuration settings.
FILE	Connects to a file or a folder. Please refer File Connection Manager article to understand the configuration settings.
FLATFILE	Connect to data in a single flat file.
FTP	Connect to a FTP server. Please refer FTP Connection Manager article to understand the configuration settings.
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.
OLE DB	Connects to a data source by using a OLE DB provider. Please refer OLE DB Connection Manager in SSIS article to understand the configuration settings.
ODBC	Connects to a data source by using ODBC.
SMOSERVER	Connects to a SQL Server Management Objects (SMO) server. Please refer SMO Connection Manager in SSIS article to understand the configuration settings.
SMTP	Connects to an SMTP mail server.
SQL MOBILE	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.

Configuring SSIS Connection Manager

In SQL Server integration Services, there are two types of Connection managers: Package Level Connection Managers and Project Level Connection Managers

Configuring Project Level Connection Manager in SSIS

If you configure Project Level SSIS Connection Manager then the connection will be available to all the package we created under that project. If you need that connection in multiple package then it is good practice to create Project Level Connection Managers in SSIS.

TIP: In real-time we mostly use Project Level Connection Managers but there are some situations where we use package level connection managers.

In this example We are going to show, How to create or configure Project Level Connection Managers in SQL Server Integration Services (SSIS)

Once you created new Project under SSIS. If you look at the Solution explorer we have few folders. For now lets concentrate on the Connection Managers Folder.

Right click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the menu.

When you click on the New Connection Manager option, an Add SSIS Connection Manager window form will be opened to select the connections managers from the list it is provided.

For the time being, We selected the OLE DB connection manager from the list. Once we selected the OLE DB connection manager then another window form of Configure OLE DB Connection Manager will be opened to configure the data connections.

If you observe the above, Data Connections pane is empty because we have not created any connection managers before. If we created any connection managers before then instead of creating them again and again we can select them here.

Click on the new button from the above screenshot will open another window form of Connection Manager to select the Provider, Server Name and Database Name.

From the above screenshot, you can observe that we are using our local host windows account as server name and [AdventureWorks2014] as the database name.

NOTE: In real-time you have to select the Use SQL Server Authentication option and provide the valid credentials given by your Admin person.

Click on the Test Connection button provided below to check whether the connection is successful or not.

Once we finish configuring the connection managers, the OLE DB connection manager name will be displayed in the Data connection pane. So select the created connection manager and click ok.

We successfully created the Project level SSIS Connection Manger.

From the above screenshot, you can observe the Project Level Connection Manager.

Configuring Package Level Connection managers

If you configure Package Level Connection Managers in SSIS then the connection will be available to that particular package. You can’t access it outside the package.

TIP: If you need that connection in one package only and you don’t use it in other package then there is no point in creating Project Level Connection Managers so, go for Package Level Connection Manager.

Right Click on the Empty Space present in the Connection Managers pane at the bottom of the package designer pane. It will pop up the context menu to select the connection manager.

This Context Menu will show some frequently used connection managers (OLE DB, Flat File, ADO.NET, Analysis Services and File Connection). If you don’t find the required one then select the New Connection option.

In this example we are creating OLE DB Connection Manager it means we can select New OLE DB Connection option from the Context Menu but if you observe from the above screenshot, We selected the New Connection option. This will help you to understand the similarities while creating SSIS Connection Managers.

When you click on the New Connection Manager option, an Add SSIS Connection Manager window form will be opened to select the connections managers from the list it is provided.

Here we selected the OLEDB Connection Manager. Again we have to follow the above mentioned step to configure the connection manager.

From the above screenshot, you can observe that there is one Project Level Connection Manager and one Package Level SSIS Connection Manager.

Thank You for Visiting Our Blog