New SSIS Features in SQL Server 2012
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’.
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.
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.
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.
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.
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
In addition, you
have the option of setting project level parameters where the parameters are
accessible for all the packages in the SSIS project.
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.
if you want to disable them follow the same path.
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 Task
are 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
the previous versions, now you can edit task components while those components
are not connected or they are in an error state.