SSIS Connection Manager

As we all know, Microsoft SQL Server Integration Services packages transfer data from different sources to different destinations, such as files, Excel Worksheets, SQL Database, and Oracle Database. Therefore, we must establish reliable connections with various data sources and destinations to extract or insert data. In SSIS, Connection Manager plays a vital role in establishing reliable connections.

Whether you’re extracting tables from databases, loading rows into a data warehouse, or transferring records from DB to text or Excel files or vice versa, SSIS Connection Manager is necessary. This SSIS article shows how to create a connection manager and the difference between project-level and package-level connection managers.

The SSIS Connection Manager for the relational database management system includes Connection Strings, the combination of Server instance Name, Database Name, provider Name, and credentials. At the same time, the File system contains information about the file location, type, delimiters, etc. It acts as a bridge between packages and source/destination, enabling data transfer.

Supported SSIS Connection Manager

The SSIS Connection Manager’s primary purpose is to provide connections within packages that can reuse in other packages or tasks. If it is package-specific, you can use it in all tasks that belong to that package. If it is project-specific, you can use it in all packages within that project.

SQL Server Integration Services provides different types of built-in Connection Managers. The SSIS package will connect to different types of Data Sources such as Oracle, Access, Excel, Text File, SQL Server, and SQL Server Analysis Services using Connection Manager. The below table shows the list of SSIS Connection Managers.

Please refer to the SSIS Cache Connection Manager article for configuration settings.

SSIS Connection ManagerDescription
ADOIt connects with ActiveX Data Objects. Or it can be called an ADO object. Please refer to the ADO article to understand the configuration settings.
ADO.NETIt connects to a data source compatible with the ADO.NET framework using a .NET provider. Please refer to the ADO.NET article.
CACHEReads data from the data flow or a cache file (.caw) and can save data to the cache file.
DQSThis SSIS Connection Manager connects to a Data Quality Services server and a Data Quality Services database on the server.
EXCELIt connects to an Excel workbook file. Please refer to Excel to understand the configuration settings.
FILEIt helps handle flat files with delimited (comma, tab, etc.) or fixed-width (ragged) formats, such as CSV or text files. It connects to a file or a folder. Please see the File article to know the configuration settings.
FLATFILEConnect to data in a single flat file.
FTPIt will connect to an FTP server. Please refer to the FTP article.
HTTPThis SSIS Connection Manager connects to a web server.
MSMQIt connects to a message queue.
MSOLAP100It connects to an instance of SQL Server Analysis Services or an Analysis Services project.
MULTIFILEIt connects to multiple files and folders.
MULTIFLATFILEThis SSIS Connection Manager connects to multiple data files and folders.
OLE DBIt uses an OLE DB provider for connecting to relational databases such as SQL Server, Oracle, MySQL, etc. Please refer to the OLE DB article to understand the configuration settings.
ODBCIt connects to a data source by using ODBC.
SMOSERVERIt connects to a SQL Server Management Objects (SMO) server. Please refer to the SMO article to understand the configuration settings.
SMTPIt connects to an SMTP mail server.
SQL MOBILEIt connects to a SQL Server Compact database.
WMIIt connects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.

Configuring SSIS Connection Manager

A package includes at least one connection manager. In SQL Server Integration Services or SSIS, there are two types of Connection managers: Package Level and Project Level Connection Manager.

Configuring Project Level Connection Manager in SSIS

If you configure the Project Level SSIS Connection Manager, the connection is available to all the packages we created under that project. When you upgrade the project to the current version, the project level connection managers will work without issues.

If you need that connection in multiple packages, creating a Project Level Connection Manager in SSIS is good practice. In real-time, we mostly use Project Level Connection Managers. But there are some situations where we use package-level connection managers.

This SSIS example shows how to create or configure Project Level Connection Manager in SQL Server Integration Services.

Once you have created a new Project, if you look at the Solution Explorer, we have a few folders. For now, let us concentrate on the Connection Managers Folder.

Go to Solution Explorer 1

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

SSIS Project Level Connection Manager 2

When you click on this option, an Add Connection Manager window opens to select the type from the list of available options, such as ADO.NET, OLE DB, Flat File, etc.

SSIS Connection Manager 3

For now, we selected the OLE DB connection manager from the list. Once we set the OLE DB, another window form of Configure OLE DB opened to configure the data connections.

Configuration 4

If you observe the above, the SSIS Data Connections pane is empty because we have not created any connection manager. If we had created them before, we could select them here instead of creating them repeatedly.

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

Choose the Database 5

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

NOTE: You must select the Use Server Authentication option in real time. Next, 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.

SSIS Project Level Connection Manager 6

Once configured, the OLE DB connection manager’s name will be displayed in the Data connection pane. So select the created connection manager and click ok.

Click the Ok button 7

We successfully created the Project level SSIS Connection Manager.

Project Level SSIS Connection Manager 8

You can observe the Project Level Connection Manager from the screenshot above.

Configuring Package Level Connection Manager in SSIS

If you configure SSIS Package Level Connection Manager, 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 and don’t use it in another, there is no point in creating Project Level Connection Managers. So, go for Package Level Connection Manager.

Right-click on the Connection Managers pane at the bottom of the package designer. It will pop up in the context menu to select the connection manager.

Package Level SSIS Connection Manager 1

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, select the New Connection option.

In this example, we are creating the OLE DB Connection Manager. We can select the New OLE DB Connection option from the Context Menu. But if you see from the above screenshot, We chose the New option. It helps you to understand the similarities while creating SSIS Connection Manager.

When you click on the New option, an Add Connection Manager window form will open to select the one from the provided list.

SSIS Connection Manager 3

Here, we selected the OLEDB Connection Manager. Again, we have to follow the steps mentioned earlier to configure it.

Package Level SSIS Connection Manager 2

The above screenshot shows one Project Level Connection Manager and one Package Level SSIS Connection Manager.

Comments are closed.