Editor's Note: This is the first of a three-part series by frequent SearchSQLServer.com contributor Robert Sheldon about new SSIS features that will make the jobs of BI developers easier -- and why.
Developers are always looking for ways to make their lives easier. That's just as true for the
Requires Free Membership to View
business
intelligence (BI) community as anyone. So, when Microsoft released SQL Server 2012 -- along
with an updated version SQL Server Integration Services (SSIS) -- the BI folks were grinning from
ear-to-ear. The updated SSIS is chock-full of new features guaranteed to improve the developer's
lot. In particular, five of those features promise to make BI development simpler and developers
more productive than ever.
SSIS 2012
NOTE: In SSIS 2012, you develop your packages in SQL Server Data Tools. In previous versions, you used Business Intelligence Development Studio. However, both development environments support a similar version of SSIS Designer.
Project connection managers
Since the release of SQL Server 2005, SSIS has included an extensive set of connection managers that let you access data from sources such as flat files, relational databases, Analysis Services databases and other source types. However, you always had to create those connection managers within the context of a package, which meant only that package could use them. Even if multiple packages required the same connection managers, you had to re-create them for each package, no matter how often it meant duplicating your efforts.
In SSIS 2012, all that's changed. Now, you can create project connection managers that are available to all packages within a project. The process for creating project connection managers is similar to creating package ones -- the difference lies in where you create them. For project connection managers, you use the new Connection Managers node in Solution Explorer, rather than the Connection Managers window in SSIS Designer.
To create a project connection manager, right-click the Connection Managers node in Solution Explorer, and then click New Connection Manager, as shown in Figure 1. This launches the Add SSIS Connection Manager dialog box. From there, you can create your project connection manager for the specific data source type, just like you've been creating package connection managers.
After you create a project connection manager, it is listed beneath the Connection Managers node in Solution Explorer, as shown in Figure 2. The connection manager shown in this figure is named AW2012. SSIS automatically added the .conmgr file extension to the name.
Once you create a project connection manager, you can use it in any package within that project. In fact, project connection managers are also listed in the Connection Managers window in SSIS Designer for each package. However, the connection manager name is preceded with (project) and the file extension is dropped. For example, the AW2012.conmgr connection manager listed in Solution Explorer appears in the Connection Managers window as (project) AW2012, as shown in Figure 3.
You can also convert a project connection manager to a package one. To do so, right-click the connection manager in the Connection Managers window, and then click Convert to Package Connection as shown in Figure 4. This will remove the connection manager from Solution Explorer and remove (project) from the connection manager name in the Connection Managers window.
Once you convert a project connection manager to a package one, you can use the connection manager only in the current package. If any other packages are using that connection manager, they will not run. However, you can also convert a package connection manager to a project one. In the Connection Managers window, right-click the connection manager and then click Convert to Package Connection. The connection manager will then be available to all packages in your project.
This was first published in August 2012
Join the conversationComment
Share
Comments
Results
Contribute to the conversation