New SSIS Features in SQL Server 2012

Server Integration Services (SSIS) has under-gone through some significant changes in SQL Server
2012 which I will outline in this article.

Connection Managers

Now you
have project-based connection managers which mean those connection will be
available for all the packages that you are creating. This avoids
recreating frequently used connections for every package. 
Those connections are created under Connection Manager in the Solution explorer
as you can see in the below image.

As in the
previous versions of SSIS, in SQL Server 2012 the connection manager will be shown
in Connection Mangers region of the package. However, now there
is additional text for project connections so users can easily
understand and take extra care when modifying them.

By right-clicking the project connection manager and selecting Convert to Package
Connection, you can demote a project connection to a package connection.
Similarly, you have the option of prompting a package connection to a project

from the above two connection types, there are two more connection types.
, namely Offline Connections and Expression Connection.

In previous versions, if a connection is invalid, every time you
open the package it will hang until the connection times out to show the error. However, in SQL Server 2012, when a connection is invalid after
the initial check, the connection will be set to offline and so avoid checking
the connection again. When the connection is ready, you can test the
connectivity and you can bring the connection online by right-clicking it.
In addition, you can set the connection to offline manually. Expression
Connections are simply parameters in variables.

The Execute
Package Task has undergone a slight change with respect to connection managers. The
Execute Package Task now has a new parameter called Reference type as shown in the
below image.

Reference is for child packages within the project and when this is selected
, you will not be shown the connections in the Connection Manager section.
External reference is for the packages outside of the project.

ODBC Support

source and ODBC destination components are available in SSIS 2012. Prefviously, there were some
difficulties in connecting to MySQL because of the unavailability of the
OLEDB drivers for MySQL. Users were forced to use OLEDB for ODBC drivers which was
comparatively slow. With ODBC support in 2012, you can directly connect to
MySQL using ODBC.

Flat File Improvements

flat files are very important and very frequent task used in SSIS. However, in
previous versions, you are unable to import text files with variable columns
and it has to have fixed number of columns.  This is what you see in in preview
if you try to import text file with a variable number of columns in previous
versions of SSIS.

Description: C:\Users\Dinesh\Desktop\Book\5528EN_06_1stDraft\Images\5528EN _06_16.png

If you
want to import these types of text files, you may have to use scripting which
is not an easy task.

in SQL Server 2012 this issue (or bug the way you prefer to call it that) is fixed as
you can see from the below image.

Pages: 1 2


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |