SSIS Connection Managers
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 Connection Managers.
In SQL Server Integration Services, Connection Managers include Connection Strings which is the combination of Server instance Name, Database Name, provider Name and credentials.
Built-in Connection Managers in SSIS
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 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 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 Managerarticle to understand the configuration settings.td> |
| 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 article to understand the configuration settings. |
| FILE | Connects to a file or a folder. |
| FLATFILE | Connect to data in a single flat file. |
| FTP | Connect to a 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. |
| OLE DB | Connects to a data source by using a OLE DB provider. Please referOLE DB Connection Manager 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 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 Connection Managers in SSIS
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 Connection Managers in SSIS 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 Managerwindow 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 ofConnection 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 Connection Manger.
From the above screenshot, you can observe the Project Level Connection Manager.
Configuring Package Level Connection manager
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 Managerwindow 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 Connection Manager.
Thank you for Visiting Our Blog
SSIS OLE DB Connection Manager
In SQL Server Integration Services, OLE DB Connection Manager enables SSIS packages to connect with Database using an OLE DB provider. For example, an OLE DB connection manager uses Microsoft OLE DB Provider for SQL Server to connect with Microsoft SQL Server.
In real-time, OLE DB Connection Manager is the mostly used connection manager while designing the SSIS packages. Following are the some of the occasions we use:
- OLE DB source use OLE DB Connection Manager to connect and extract data from the Database
- OLE DB Destination use OLE DB Connection Manager to connect and extract data from the Database
- Term Lookup Transformation only support OLE DB Connection Manager to connect with the term lookup table (or Reference table).
- Term Extraction Transformation only support OLE DB Connection Manager to add the Exclusion List.
- Execute SQL task use OLE DB Connection Manager to connect and extract data from the Database
- OLE DB Connection Manager is also helpful to access data from the unmanaged code.
Configuring OLE DB Connection Manager in SSIS
In this example We are going to show, How to create or configure OLE DB Connection Managers in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have four 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 context menu.
When you click on the New Connection Manager option, an Add SSIS Connection Managerwindow form will be opened to select the connections managers from the list it is provided.
Here we selected the OLEDB connection manager from the list. Once we selected the OLEDB connection manager then another window form of Configure OLEDB 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, it will open another window form ofConnection 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 OLEDB connection manager name will be displayed in the Data connection pane. So select the created connection manager and click ok.
Click ok button to finish configuring the OLE DB Connection Manger.
From the above screenshot you can observe that, We successfully created OLE DB Connection Manager in SSIS.
Thank you for Visiting Our Blog
ADO Connection Manager in SSIS
In SQL Server Integration Services, ADO Connection Manager enables SSIS packages to connect with ActiveX Data Objects (In short ADO Objects). For example, if we want to connect with the data source written in previous versions (VB 6) then we can use this ADO Connection Manager.
Configuring ADO Connection Manager in SSIS
In this example We are going to show, How to create or configure ADO Connection Managers in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have four 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 context 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.
Here we selected the ADO connection manager from the list. Once we selected the OLEDB connection manager then another window form of Configure OLEDB Connection Manager will be opened to configure the data connections for ADO connection manager.
If you observe the above, Data Connections pane is not empty because we already created few connections before that’s why they are displaying. If you are creating the ADO Connection Manger for the first time then Data Connections pane will be empty. If you want you can select the connection manager from the list or else you can create New ADO Connection Manager by clicking on the New button.
Click on the new button from the above screenshot, it will open another window form of Connection Manager to select the Provider, Server Name and Database Name.
In this example we are connecting with SQL Server so let us select the SQL Server Native Client from the Provider drop down list.
Please provide the SQL Server instance Name and then select the Database from the drop down list.
From the above screenshot, you can observe that we are using our windows account as server name and [TRANSFERDBTASKS] 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 ADO connection manager name will be displayed in the Data connection pane. So select the created connection manager and click ok.
Click ok button to finish configuring the ADO Connection Manger.
From the above screenshot you can observe that, We successfully created ADO Connection Manager in SSIS.
Thank you for Visiting Our Blog
ADO.NET Connection Manager in SSIS
In SQL Server Integration Services, ADO.NET Connection Manager enables SSIS packages to connect with Database using managed .NET provider. For example, an ADO.NET connection manager uses SqlClient data provider to connect with Microsoft SQL Server. Following are the some of the important properties of the ADO.NET Connection Manager:
- ADO.NET Connection Manager uses a managed .NET provider to access the Database.
- This Connection Manager is very useful to access the data source written in managed code.
- ADO.NET Connection Manager in SSIS is normally used to access Microsoft SQL Server
- SSIS ADO.NET Connection Manager Supports many managed provides such as SqlClient, ODBC and OLE DB.
- OLE DB provider in SSIS ADO.NET Connection Manager allows us to access the data sources supported by OLE DB.
- In real-time, SqlClient data provider is the most commonly used .NET providers.
NOTE: Using OLE DB provider in SSIS ADO.NET Connection Manager will affect the performance because it has to add extra managed larger on the OLE DB (Unmanaged provider).
Configuring ADO.NET Connection Manager in SSIS
In this example We are going to show, How to create or configure ADO.NET Connection Managers in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have three 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 context 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.
Here we selected the ADO.NET connection manager from the list. Once we selected the ADO.NET connection manager then another window form of Configure ADO.NET 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, it 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 selected the SqlClient Data Provider.
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 ADO.NET connection manager name will be displayed in the Data connection pane. So select the created connection manager and click ok.
Click ok button to finish configuring the ADO.NET Connection Manger.
From the above screenshot you can observe that, We successfully created ADO.NET Connection Manager in SSIS.
Thank you for Visiting Our Blog
Cache Connection Manager in SSIS
In SQL Server Integration Services, Cache Connection Manger is used in the Cache Transformation. Lookup Transformation uses this connection manager to perform lookup operations using the cache file. Please refer Lookup Transformation in Full Cache Modearticle to understand, How to use Cache file in Lookup Transformation.
Cache Connection Manger in SSIS performs 2 operations:
- Cache Connection Manger reads data from the Cache Transformation and save the data in cache file with extension of .caw.
- Cache Transformation reads data from the cache file using Cache Connection Manger
Configuring Cache Connection Manager in SSIS
In this example We are going to show you, How to create or configure Cache Connection Managers in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have three 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 context 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.
Here we selected the Cache connection manager from the list. Once we selected theCache connection manager, another window form of Cache Connection Manager Editor will be opened to configure the Cache connection manager.
General Tab
General tab in the Cache Connection Manager Editor box provides an option: whether you want to save the data into cache file or you want to read the data inside the cache file. Following are the options available in the general tab
- Connection manager name: Please provide the name for the Cache Connection Manager as per the company requirements.
- Description: Please provide the valid description about the connection. This information will be useful for the others to understand what this cache connection doing
- Use file cache: Please check mark this option to use the cache file. If you check mark this option then the browse button and file box will be opened to browse required file.
- File name: Cache File path and file name will be displayed here. (Or address of the cache file)
- Browse: Once you click on this button, it will open a new window to select the required file name. Either you can select the already existing cache file or else you can create new cache file.
- Refresh Metadata: Deletes the old metadata of the columns and then regenerate new metadata.
From the below screenshot you can observe that we changed the Name and Description of the Cache Connection Manger. Now, Click on the Browse button to select the Cache file.
Once you click on the Browse button a new window will opened to select the existing cache file or create new file.
From the above screenshot you can observe that, we already have one cache file in the folder. To create new one just type the name of the file at File name as we done above (Lookup File). This will create new cache file.
Now, Let us see how to select the existing file
Click on the open button to select the cache lookup.caw file.
We finished selecting the existing cache file using cache connection manager. Now we have to configure the column tab
Index Position: We have to specify the Index position of each and every column present in the cache file. Index position will be 0 for all the non-index columns. For all the index columns, index position will be positive number.
This is the most important property for the Lookup Transformation because Lookup Transformation will perform lookup on the columns with positive index. For this example Country Name column has unique values so we changed the index position to 1 for this column.
Click ok button to finish configuring the Cache Connection Manger.
From the above screenshot you can observe that, We successfully created Cache Connection Manager in SSIS.
Thank you for Visiting Our Blog
Excel Connection Manager in SSIS
In SQL Server Integration Services, Excel Connection Manager is used to connect SSIS Packages with the Excel files.
Configuring Excel Connection Manager in SSIS
In this example We are going to show, How to create or configure Excel Connection Managers in SQL Server Integration Services (SSIS)
Below screenshot shows you the Data present in the Excel File
Once you created new Project under SSIS. If you look at the Solution explorer we have three 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 context 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.
Here we selected the Excel connection manager from the list. Once we selected the EXCEL connection manager then another window form of Excel Connection Manager will be opened to configure the file path.
Click on the Browse button from the above screenshot, it will open new window to browse the drive.
From the above screenshot you can observe that, we selected the COPY PRODUCTS.xls file and the file is in Microsoft Excel 97-2003 format. So we didn’t changed the Excel Version.
If you observe the data present in the COPY PRODUCTS.xls file. Its first row has the column names like color, Product Name and Price etc. So Please check mark the First row has column names option.
Click ok button to finish configuring the Excel Connection Manger.
From the above screenshot you can observe that, We successfully created Excel Connection Manager in SSIS.
Thank you for Visiting Our Blog
File Connection Manager in SSIS 2014
In SQL Server Integration Services, File Connection Manager enables SSIS packages to connect with the Files and Folders present in the File System. This Connection Manger not only allows us to connect with existing files and folder but also creates New files and folders at run time.
TIP: In SSIS, File System Task uses File Connection Manger only to connect with the Files and Folder.
Configuring File Connection Manager in SSIS
In this example We are going to show, How to create or configure File Connection Manager in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have three 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 context 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.
Here we have to select the File Connection Manager from the provided list
Once you selected the Connection Managers for files, File Connection Manager Editor will be opened in new window. Using this editor we have to configure the connection with files and folders.
File: This text box will display the full path of the selected file or folder.
Browse: This button will be used to traverse the file system to locate the required file or folder.
Usage Type: File Connection Manager provides us four different options to connect with the files and folders. Below table explains their functionality in detail:
| Usage Type | Description |
|---|---|
| Create File | This option will create new file at run time. For example, If we are renaming the file then we have to create new file with new name. |
| Existing File | This option will be helpful to work with already existing file in the file system. For example, If we want to copy or move file from one location to other then, we have to select this option. |
| Create Folder | This option will create new folder at run time. For example, If we want to copy or move directory content from one location to other, then we can create new folder with better name by select this option. |
| Existing Folder | This option will be helpful to work with already existing folder in the file system. For example, If we want to copy or move directory from one location to other, then we can use this option to select the required folder. |
If you select the Existing File option then, When you click on Browse button it will open theSelect File window to select the existing files as shown in the below screenshot.
If you select the Existing Folder option then, When you click on Browse button it will open the Browse For Folder window to select the existing folders as shown below.
Once you selected the required file, Click on the Ok button
Click Ok button to finish configuring the File Connection Manger.
From the above screenshot you can observe that, We successfully created File Connection Manager in SSIS.
Thank you for Visiting Our Blog
SMO Connection Manager in SSIS
The SMO (SQL Server Management Objects) Connection Manager is used to establish connection with SQL Management Object server. In SQL Server Integration Services, all the Database related tasks uses SMO Connection Manager to connect with SQL server objects. For example, Transfer SQL Server Objects Task in SSIS uses SMO Connection Manager to connect with the server.
Configuring SMO Connection Manager in SSIS
In this example We are going to show, How to create or configure SMO Connection Manager in SQL Server Integration Services (SSIS)
Once you created new Project under SSIS. If you look at the Solution explorer we have three 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 context 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.
Here we selected the SMOServer connection manager from the list. Once we selected the SMO connection manager then another window form of SMO Connection Manager Editor will be opened to configure the connection.
SMO Connection Manger provides following options:
- Server Name: Please specify the SQL Server instance name
- Refresh: Clicking on this button will refresh the SQL Server instances available in the Network.
- Use Windows Authentication: If we select this option then, SMO Connection Manager will connect with the SQL Server instance using Windows Authentication. If you are practicing or learning at home then select this option. In real-time we mostly don’t use this option.
- Use SQL Server Authentication: If we select this option then, SMO Connection Manager will connect with the SQL Server instance using SQL Server Authentication. In real-time this is the option we use. If you selected this option then you have to provide the User name and Password.
- User Name: Please provide the user name to access the SQL Server. Your organization will provide these credentials.
- Password: Please provide the password to access the SQL Server.
- Test Connection: Click on this button to test our connection.
From the above screenshot you can observe that, We selected Windows Authentication to connect with SQL Server instance (SURESH). In real-time you have to select the Use SQL Server Authentication option and provide the valid credentials given by your Admin person. So let us provide the same here to show you how it works.
Click on the Test Connection button provided below to check whether the connection is successful or not.
Click ok button to finish configuring the SQL Server Management Object Connection Manger.
From the above screenshot you can observe that, We successfully created SMO Connection Manager in SSIS.
Thank you for Visiting Our Blog
SSIS Connection Manager
After the proper understanding the BIDS it's time to understand the connection manager of SSIS package. In this article I am trying to cover the connection manager and the purpose of it.
The Connection Manager
The Connection manager dialog box helps us to select the type of connection to add to a package. The connection contains connection string that point to files or databases.
Available connection manager
SSIS uses connection managers to integrate different data sources into packages. SSIS includes a wide variety of different connection managers that allow us to move data around from place to place.
The details are mentioned bellow.
Connection Manager
|
Handles
|
ADO
|
Connecting to ADO objects such as a Recordset.
|
ADO.NET
|
Connecting to data sources through an ADO.NET provider.
|
CACHE
|
Connects to a cache either in memory or in a file
|
MSOLAP100
|
Connecting to an Analysis Services database or cube.
|
EXCEL
|
Connecting to an Excel worksheet.
|
FILE
|
Connecting to a file or folder.
|
FLATFILE
|
Connecting to delimited or fixed width flat files.
|
FTP
|
Connecting to an FTP data source.
|
HTTP
|
Connecting to an HTTP data source.
|
MSMQ
|
Connecting to a Microsoft Message Queue.
|
MULTIFILE
|
Connecting to a set of files, such as all text files on a particular hard drive.
|
MULTIFLATFILE
|
Connecting to a set of flat files.
|
ODBC
|
Connecting to an ODBC data source.
|
OLEDB
|
Connecting to an OLE DB data source.
|
SMOSever
|
Connecting to a server via SMO.
|
SMTP
|
Connecting to a Simple Mail Transfer Protocol server.
|
SQLMobile
|
Connecting to a SQL Server Mobile database.
|
WMI
|
Connecting to Windows Management Instrumentation data.
|
How to use Connection Manager
To create a connection manager just right click it and choose one from the shortcut menu.
Here I choose New Connection… The Connection manager dialog box appears.
From depending on types, we can choose the connection.
Where It Used
The connection manager can be used at data flow source and destination.
Here some pictorial diagram showing that the connection manager is using at Data Flow sources.
Related tropics
Understand the BIDS
