SSIS Connection Managers

A significant part of working with data flow is to define source and destination connections, and we might just as well define those before anything else. Our source data is coming from the CRONUS demo database, DD_W1_01, and they are migrated into the destination database, Stage. In this training environment both are located at the same SQL Server, but in real life they would more likely be located at different servers.

At the lower part of the design window there is an area where we can define new connections by right clicking the area. Right click and select New OLE DB Connection…

In the following dialogue box, click New to create a new Data connection. For the CRONUS database, the provider should be a Native OLE DB\SQL Server Native Client 11.0.

Note: From January 2024, Microsoft recommends using the Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server. See this article: Installing SQL Server Native Client.

Click OK twice to complete the definition of the Connection Manager for the CRONUS database.

Repeat these steps to define a Connection Manager for the destination, the SQL Server Stage database. Actually, since this connection was defined for the Data Source connection for our Multidimensional project, it may already be on the list of available connections.

At this time the two newly created Connection Managers should appear on the list of available connections.