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.

Because SSIS is a development tool, and the updates are mostly of a technical nature, trying to explain their business value is quite challenging. Putting it simply, the main value to business is that with the updates, development will be easier and therefore faster.

I will focus on a few of the development improvements about which I'm the most excited.

Visual Studio 2010

Business Intelligence Development Studio (BIDS) has been replaced with SQL Server Data Tools, which uses the core of Visual Studio 2010. This does not just apply to SSIS but the whole BI development environment. This is due to Microsoft's internal realignment of their internal product delivery cycles which should help reduce the mismatch between functionality in related tools. This makes deployments much simpler and integration with Team Foundation Server 2010 a lot smoother.

Ability to debug Script Tasks

In previous versions of SQL Server, you had the ability to debug Script Components but not Script Tasks. With the release of SQL Server 2012, this is no longer the case: you can forget about having to output to the console to try and figure out where exactly your code is failing.

Change Data Capture

Although Change Data Capture (CDC) is not is not new to SQL Server, there are now CDC Tasks and Components within SSIS that make it easier to implement.

Undo and Redo

At long last you are now able to undo or redo any actions – such as bringing back the data flow that you accidently deleted – without having to reload the whole project. In my opinion this improvement alone makes it worth upgrading!

Flat File Source Improvements

Two great additions to SQL Server 2012 that will solve a lot of headaches when importing data from flat files are the support for varying numbers of columns and embedded text qualifiers.

Project Connection Managers

Gone are the days where you had to recreate connections to your source and destination within each SSIS package. Connections can now be set up at a project level which can then be shared within the packages.

Column Mappings

In SQL Server 2012, SSIS is a lot smarter about how it deals with column mappings and now uses the column names instead of the lineage ID. This means that if you decide to recreate your data source task, you do not have to remap all the columns as was the case in the past. SQL Server 2012 also comes with a Resolve Column Reference Editor which allows you to link unmapped output columns to unmapped input columns across the whole execution tree; in the past this had to be done from task to task.

Parameter Handling

Parameters are a new addition to SSIS and are very useful. In the past you had to use configurations which could only be assigned at a package level. Parameters can now be set at both a package and project level. You can assign three different types of values to parameters, namely Design default, Server default and Execution.

There are quite a few more additions to SSIS (including its built-in reporting capabilities, improvements to the user interface, and integration with Data Quality Services), but the features I have focused on in this post are improvements to issues that I have frequently come across on previous projects. I'm sure these improvements and additions to SSIS will be greatly appreciated by the industry.

Sometimes you will receive this error message on Windows Server 2008:

The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine

As you know, this is just simple problem. IIS on Windows Server 2008 was running its application pool in 64 bit mode. There are no 64 bit Jet drivers. Simply changing the application pool to run in 32 bit mode enables support for the 32 bit Jet drivers.

To do this right click on the target application pool in IIS, select Advanced Settings and change Enable 32-Bit Applications to True.

If you want to check that you have the latest version of the Jet drivers goto c:\Windows\SysWOW64, right click on the Msjet40.dll file, select the details tab and view the version number. At the time of writing the latest version was 4.0.9704.0. You can view more details on this process at http://support.microsoft.com/kb/239114.

Hope it help!