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…
Load flat file data into SQL Server Table
Load Excel file data into SQL Server Table
