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

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.

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


  12. Click the Advanced icon to move to the Advanced page of the dialog box
  13. Click the New button.
  14. Change the Name of the new column to DepartmentName.
  15. Click OK.
  16. Right-click the DepartmentList Connection Manager and select Copy.
  17. Right-click in the Connection Managers area and select Paste.
  18. Click on the new DepartmentList 1 connection to select it.
  19. 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 ASP.net 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.
https://sites.google.com/site/rkkumardotnet/
Read moreRead more about Contributor