Microsoft SQL Server 2012 Bible

Exploring the SSIS Environment in More Detail

The first section of this chapter gave a quick overview of some basic elements of SSIS. The following section offers a more complete guide to the features of SSIS.

Using Connection Managers

Recall that a connection manager is a wrapper for the connection string and properties required to make a connection at run time. The following sections represent types available and configuration options to consider

What's New in SQL Server 2012

The keystone added feature of SSIS in SQL Server 2012 is the Project Deployment Model, where Projects are now the basic unit of SSIS. To this end, there is now the ability to create Connection Managers at the Project level. You create these once, read from all packages in the Project unlimited. The shared Connection Managers appear bolded inside the packages. Shared Connection Managers have a node under the Project in Solution Explorer. Right-click this node and select New. The Connection Manager Dialog boxes are the same whether you configure a Connection Manager at the project or package level.

Using Database Connection Managers

Defining database connections through one of the available connection managers requires setting a few key properties:

• Provider: The driver to be used in accessing the database
• Server: The server or filename containing the database to be accessed
• Initial Catalog: The default database in a multidatabase source
• Security: Database authentication method and any username/password ...