Working with Connection Managers

SSIS uses connection managers to integrate different data sources into packages. SSIS includes a wide variety of different connection managers that allow you to move data around from place to place. Table 1 lists the available connection managers.

Connection Manager



Connecting to ADO objects such as a Recordset.


Connecting to data sources through an ADO.NET provider.


Connects to a cache either in memory or in a file


Connecting to an Analysis Services database or cube.


Connecting to an Excel worksheet.


Connecting to a file or folder.


Connecting to delimited or fixed width flat files.


Connecting to an FTP data source.


Connecting to an HTTP data source.


Connecting to a Microsoft Message Queue.


Connecting to a set of files, such as all text files on a particular hard drive.


Connecting to a set of flat files.


Connecting to an ODBC data source.


Connecting to an OLE DB data source.


Connecting to a server via SMO.


Connecting to a Simple Mail Transfer Protocol server.


Connecting to a SQL Server Mobile database.


Connecting to Windows Management Instrumentation data.

Table 1 : Available Connection Managers

To create a Connection Manager, you right-click anywhere in the Connection Managers area of a package in BIDS and choose the appropriate shortcut from the shortcut menu. Each Connection Manager has its own custom configuration dialog box with specific options that you need to fill out.

Try It!

To add some connection managers to your package, follow these steps:

  1. Right-click in the Connection Managers area of your new package and select New OLE DB Connection.
  2. Click New to create a new data connection.
  3. In the Connection Manager dialog box, select the SQL Native Client provider.
  4. Select your test server and provide login information.
  5. Select the Chapter16 database.
  6. Click OK.
  7. In the Configure OLE DB Connection Manager dialog box, click OK.
  8. Right-click in the Connection Managers area of your new package and select New Flat File Connection.
  9. Enter DepartmentList as the Connection Manager Name.
  10. Enter C:\Departments.txt as the File Name.
  11. Check the Column Names in the First Data Row checkbox. Figure 1 shows the completed General page of the dialog box.

    Figure 16-4: Defining a Flat File Connection Manager

  12. Figure 1: Defining a Flat File Connection Manager

  13. Click the Advanced icon to move to the Advanced page of the dialog box
  14. Click the New button.
  15. Change the Name of the new column to DepartmentName.
  16. Click OK.
  17. Right-click the DepartmentList Connection Manager and select Copy.
  18. Right-click in the Connection Managers area and select Paste.
  19. Click on the new DepartmentList 1 connection to select it.
  20. Use the Properties Window to change properties of the new connection. Change the Name property to DepartmentListBackup. Change the ConnectionString property to C:\DepartmentsBackup.txt.

Figure 2  shows the SSIS package with the three Connection Managers defined.

Figure 16-5: An SSIS package with two Connection Managers

Figure 2: An SSIS package with two Connection Managers

Mr. Ravi Krishna - Senior Software Engineer
I am a Mocrosoft Developer and MCP Certified professional. I have overall 5 years of experience in IT Industry,in that 3 years experience in Microsoft BI(SSAS,SSIS,SSRS). I have experience on various business domains like Automation and Chemical.
Read moreRead more about Contributor