Site navigation

Social networks

Contact us

Top 10 improvements in SSIS 2012

SQLServer Integration Services (usually referred to as SSIS) are considered as a set of graphic, console utilities and program objects that are intended to extract, transform and load data from variable data sources (MySQL, Oracle, Hadoop, XML, dbf, Excel, etc.).

Here are the Top10 advantages (IMHO) introduced into SSIS 2012.

#1 Undo/Redo
It is really incredible, but the package designer of the previous versions of SQL Server didn’t provide the opportunity to perform undo\redo operations. To make it clear, just imagine that you’ve accidentally deleted some paragraph while editing text in Word, and the only way out is to retype it once again.
It used to be a common practice for SSIS Designer. Once a developer had changed formatting or introduced any alterations into a package, there was no way to undo the changes. Moreover, SSIS Designer would automatically save all the changes as soon as you had the package run.
Now SSIS Designer supports up to 20 undo\redo operations.

#2 Configuration and Deployment of Packages with T-SQL
Now you have a set of stored procedures at hand, which give you opportunity to configure and deploy packages with the help of T-SQL. Well, SSIS have become even closer to a database. You can create stored procedures of any level of complexity. For instance, you can select data from a table and send them in different packages as parameters.

#3 Connection Manager
Now you can make a connection for the whole project. Formerly, you had to copy a connection from one package to others. Also, to change a server name, you no longer need to amend all the packages in the project. You can even mark the connection as «Work Offline» in the designer, and it’ll significantly simplify the debugging. There is also support for parameters for connections.
One more advantage – you can load the cache with a master package, and all the child packages will share the same in-memory cache.

#4 Parameters and Workspace
There is no need to use configuration files or tables to transfer parameters into a package. A developer does not have to worry about a bunch of configuration files that used to exist separately from packages.

#5 Flat File Source improvements
The Flat File Source now supports import of text files with a varying number of columns.
Even the lines like ‘What’s new in SSIS’ are imported correctly.

#6 Debugging in Script Component
True developers do not use debuggers! :)
Script Task and Script Component now have a support for .Net 4.0, and you can carry out debugging right in Script Component.

#7 ODBC support
ODBC is dead?! ODBC is as large as life!
Microsoft keeps up with ODBC (de facto a standard for databases).
In all fairness, I must say that there previously was a support for ODBC by means of ADO.Net, but the speed of such a connection left much to be desired.

#8 Reporting
Have you ever found yourselves biting your nails trying to find out why a SSIS-package turned out to be an error or was being run for 3 hours instead of 5 minutes? I can hardly call it a thrilling thing to check the log file amounting to several megabytes. Now SISS has built-in reports to allow troubleshooting and monitoring efficiency.

#9 Data Taps
Imagine a Data Viewer that can be added without introducing any changes to a package, that can write data to the disk and not only show it on the screen. Now you can fix SSIS-packages even with no rights to access the data source.

#10 Back up
SSISDB directory is a central point for working with projects of SSIS 2012 services.
The objects being stored in the SSISDB directory include projects, packages, parameters, environment, and log file. It is enough just to make a «backup» of SSISDB database using standard tools of SQL Server.
The previous SSIS version provided easy backups only for the packages stored in the system database msdb, but configuration files related to these packages used to be stored in the file system – and these files were not copied while backing up msdb database.
Andrey Kudievskiy

ABOUT

WEEZLABS

Not just another web and mobile apps development company!

WeezLabs is about dreaming big and helping our clients reach their full potential.

LEARN MORE

VISIT OUR OFFICE:

1848 Lincoln Blvd, Suite #100,
Santa Monica, CA 90404

TELL US HOW

WE CAN HELP

Call us 310.776.6234 or complete the form below.