This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

• Tables and Schemas in the data warehouse
• Dimensions and Slowly Changing Dimensions
• Fact tables and measures
• Intro to columnstore indexes
• Columnstore indexes and partitioning
• Introduction to SQL Server Data Tools (SSDT)

One of the first items you’ll create when developing a new project in SQL Server Integration Services (SSIS) is a connection manager.  Connection managers allow control flow and data flow tasks within SSIS packages to access a variety of data sources and destinations.  Depending on the type of connection manager you create, you’ll be able to access relational database sources like SQL Server or Oracle, web services, external files, FTP Servers.  The connection manager you select will not only determine what data store you can access, but it can also determine what functionality is available to you, so choose wisely.

The following connection managers are built-in:

• SQL Server
  • ADO.NET (preferred when using stored procedures or parameterized queries for its usability and better support for a larger range of data types)
  • ODBC
  • OLE DB (deprecated, use ODBC instead)
• SQL Server Compact Edition

• SQL Server administrative tasks
  • SMO

• File operations
  • File or Multiple File
  • Flat File or Multiple Flat File
  • FTP

• Other
  • Analysis Services
  • DQS (Data Quality Services)
  • Excel
  • HTTP
  • MSMQ (Microsoft Message Queueing)
• SMTP
• WMI (Windows Management Instrumentation)

Once you’ve created a connection manager, you can reuse it as often as needed within the package/project, in a variety of roles.  For example, an ODBC connection to the AdventureWorks2012 database can be used as the source for a data flow task, a data flow destination, the connection for an Execute SQL Task, etc.  It’s not necessary to create a separate connection manager for each task.

32-bit or 64-bit

One of the most confusing aspects about developing SSIS projects for newbies is when you’re running 32-bit and when you’re running in 64-bit.  The SSDT development environment uses 32-bit providers.  So you’ll only see those providers where a 32-bit version of the adapter was installed on the workstation on which you’re developing.  When you execute the project outside of the development environment, the version of the adapter is determined by the OS you’re running on.  As you start to develop projects in SSDT, you may notice a project property called Run64BitRuntime.  Don’t be confused, this property is only used at design-time, not at runtime.

Scope

Within SSIS, there is a hierarchy of scope.  For example, a project can contain one or more packages.  Those packages can contain one or more connection managers.  Additionally, the project itself can contain one or more connection managers.  Where a connection manager is available is dependent on where it was created.  A package-scoped connection manager is created within the scope of a specific package and may only be accessed by tasks within that package.  Connection managers that are project-scoped, however, are available to tasks in any package within the project.  If you define both a project-scoped and a package-scoped connection manager with the same name, the package-scoped connection manager’s settings will take precedence within that package.

Summary

Connection managers are relatively simple, yet critical components in SSIS development. Familiarize yourself with the various types available and the pros and cons of each.  And bear in mind that third-party connection managers are also available.