Implementing a data warehouse: SSIS Connection Managers

Originally published on ColleenMorrow.com.

This post is part of a series on implementing a data warehouse with Microsoft SQL Server 2012. So far, we’ve covered:

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.

About the Author

SQL Server Consultant

Colleen Morrow

Colleen Morrow is SQL Server Consultant at UpSearch.

Colleen is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft’s SQL Server to protect, optimize and unlock data’s value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *