Updated March 14, 2023
Definition of SSIS Connection Manager
Connection managers for SSIS are being used to set up a link between SSIS and an external data source. SSIS includes several distinct types of connection managers. Generally, SSIS packages interface with a multiple data sources. Out of the package, all of the most popular sources of data are enabled. Just onset of design, there was a Connection Managers section where we may define every data source that we use. Most SSIS jobs involve the use of a connection manager.
What is SSIS connection manager?
A connection manager comprises the Connection String property, which we establish at the design stage; at program execution, the value in the connection string property is used to construct a physical connection. A connection manager can be set up at either the packages or project level. All of the packages inside the project have access to the connection manager that was established at the construction projects. A connection manager built at the package level, on the other hand, is only available to that package.
New Project SSIS Connection
To distribute connections to providers, connection managers are built at the design stage instead of data sources. The Integration Services project should use the project deployment model to install a link manager at the design stage. The Connection Administrators directory emerges in Solution Explorer whenever a project was set up to use this paradigm, and the Data Sources folder is eliminated. Project connections were added to SSIS by Microsoft, which allows creating connections that were available throughout all packages inside a project. Designers could now make connections at the design stage rather than having to replicate each connection manager in each package. All packages in a project’s connections will appear in the connection manager tray instantly.
Each projects connection’s name is typically prefixed with the designation (project) to show clearly that this is a project connection, as illustrated. Package connections are those that don’t have this identifier and can only be accessed from within that package. Project connections would also be visible under the Connection Managers tab in the Solution Explorer window.
After that, right-click on the Connection Managers node and build a new project connection. In the very same box, users may change or eliminate a current project connection manager. Switch to Package Connection is available by right-clicking on a project connection. Nevertheless, we must exercise caution when selecting this choice. When we change a project connection to a package connection, it’ll only be displayed in the packages where the user is currently operating.
One could still use dynamic attributes like statements and parameters whether we are utilising project connections. Please remember that if configuring a project connection with parameters, then have to use project parameters instead of package variables. Since the latter is not usable outside of the context of a single package, it really would not always be available for application connections. While configuring project links, the expressions builder’s UI restricts users to only project parameters.
Add OLE DB SSIS connection manager
The attributes that were utilized to build a physical connection at design time will be used at runtime to execute the SSIS package. The connection managers option at the bottom of the main screen can be used to create SSIS connection managers:
Then, right-click inside the tab panel and select the connection manager type from the context menu strip to create a new connection manager.
OLEDB is the acronym for Object Linking and Embedding, Database. It is a Microsoft API that enables people to browse a range of data sources in a consistent way. OLE DB connection managers are perhaps the most common of all SSIS connection managers. The following box shows when we select Add OLE DB connection from the context menu below.
To create a new link, click the new button. The primary OLE DB connection configuration form is shown in the figure below:
The following are the most important OLE DB connection characteristics:
1. The OLE DB provider used to connect to the data source is called a provider.
2. Name of the server: The server to which we wish to connect
3. Type of authentication: The parameters needed to establish the connection’s security
4. Name of the database: The name of the database to which we want to connect. A default database is preferred if no specific property is been mentioned.
SSIS connection manager types
Different types of connection managers are available in SQL Server Integration Services, allowing applications to attach to a range of data sources and servers:
- When we install Integration Services, Setup installs built-in connection managers.
- Connection managers can be downloaded from the Microsoft website.
- If the available connection managers do not suit your needs, we can design a bespoke connection manager for the business.
Few are listed below:
- ADO connection: An ADO device enables a package to communicate with ActiveX data objects (ADO), including a recordset. This connection administrator is most commonly found in customized tasks written in a previous version of the program.
- ADO.NET connection: An ADO.NET connection allows a package to connect to sources of data using a.NET provider.
- EXCEL CONNECTION: The Excel connection allows the programme to read or write data from the existing Excel worksheet.
- FILE CONNECTION: A file service allows a package to interact with an existing file or directory during the run – time, or to build a new file or folder. The file could be in either XML or Excel format.
- FLAT FILE CONNECTION: A flat file connectivity allows a program to access information from a data flat file.
- ODBC Connection: An ODBC device enables a program to access open databases via the open database connectivity (ODBC) standard.
- OLEDB Connection: An OLEDB connection allows a product to link to a data source through the use of an OLEDB provider. The Microsoft OLEDB provider for SQL Server can be used by an OLEDB connection manager which connects to the SQL server.
Conclusion
To summarise, SSIS’s project connection is a powerful resource for establishing connections at the process level and reducing the additional necessary work for exchanging connections across many packages in a project. We now know where and how to alter the connection setup efficiently and precisely, thanks to this explanation.
Recommended Articles
This is a guide to SSIS Connection Manager. Here we discuss the definition, What is SSIS connection manager?, Examples with code implementation. You may also have a look at the following articles to learn more –
