SQL 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 connection.

Apart 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.



Project 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

ODBC 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

Importing 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.



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

However, 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.



These kinds of text files are available in legacy systems such as COBOL. In such systems, there will be several different types of data in the same file. For example, Order file master details and transaction details will be in a same file. The only way you can distinguish them by the record type. For master records it will be ‘M’while for detail records it will be ‘D’.

Since the column records are different (i.e. for master records you will have customer id, date etc for detail records you will have product code, quantity, unit price, unit etc) you will need the facility to support variable columns.

Variables

You will have surely experienced difficulties when it comes to configuring variables in previous versions of SSIS. In SSIS 2012 the handling of variables has undergone significant improvements.



In SQL Server 2012, variable scope is handled different than previous versions. In previous versions, the default scope is the task which you are currently in. This led to many issues in past. If you really want to change it you could click the button at the end of row and modify the scope of the variable.

As we saw in connection managers, variables with expression now have a different icon, so that users have the ability to distinguish expression variables from others. This is very handy when it comes to trouble shooting.

Parameters

Parameters are read only variables which means you can’t change them from the package execution. Now parameters are in the package tab.



The most important feature of a parameter is the Required option. If it is set to True, you have to pass a value to that parameter. If the parameter is not passed default value will not be evaluated. By using this, you can avoid mistakes when moving from one environment to the other.

If you set the Sensitive parameter to True, you won’t be able to see the parameter value. As shown in the above image – for password parameter this is a valuable option.

In addition, you have the option of setting project level parameters where the parameters are accessible for all the packages in the SSIS project.

Data Viewer

Enabling data viewers in previous versions of SSIS required quite a bit of effort. With SQL Server 2012 SSIS, simply right click the data flow path and select Enable Data Viewer and you are done.



Similarly, if you want to disable them follow the same path.

Tasks

Before discussing about new tasks let us discuss about the tasks you won’t see in SQL Server 2012. ActiveX Script Task and Executes DTS 2000 Package Taskare removed from the SQL Server 2012. Since Microsoft has stopped supporting SQL Server 2000, it has now stopped support for DTS 2000 package execution. If you are seriously thinking about moving to SQL Server 2012, make sure you have taken steps to convert those DTS’s in SQL Server 2000 to SSIS packages.

Unlike in the previous versions, now you can edit task components while those components are not connected or they are in an error state.