SQL Server bulks up
Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master
Control-flow containers are graphical representations of operations that would otherwise take developers dozens of lines of code and many hours of debugging to create. Some of the operations you can perform with the new containers (namely Sequence, For Loop, and Foreach) can’t even be done in DTS or would be too difficult to manage.
SSIS also extends the reach into different platforms, and not just different databases. For example, whereas DTS provided limited visibility into OLAP and practically none into data mining, these are natively supported in SSIS, meaning you can run data-mining queries, work with slowly changing dimensions, and send the data wherever you like, even to Reporting Services reports. In fact, SSIS serves as a bridge between SSAS and SSRS and any ODBC-compliant data source.
Package configurations are another huge enhancement. These allow you to define certain elements of the package -- file locations, database connections, special log-ins, and such -- to be read in from an external source (like an XML file) and applied at run time. This function lets you deploy packages very quickly because you don’t have to change them to move them from one system to another; you simply change the dynamic elements in the configuration and the package will run on the new server.
Last but not least, the new .Net integration releases SSIS from DTS’s dependence on the limits of VBscript and moves serious coders into the richness of VB.net. For this reason, file and string manipulations, math operations, and Win32 calls are much easier and faster.
As you would expect, the increased power and flexibility of SSIS come at the price of increased complexity. SSIS presents a learning curve that can seem daunting, but after you learn how to use it, SSIS will change the way your company thinks about its data. Systems that couldn’t communicate before are now perfectly integrated and have the full power of .Net behind them. Complex data load operations into warehouses and disparate systems will take a fraction of the time to build, execute, and support. Other complicated tasks can also be performed much faster, which means deadlines will be met more easily and projects won’t be held up because of the limited functionality of DTS and the way operations have to be taped together.
Microsoft has also made some dramatic changes to its management tools, replacing Enterprise Manager and Query Analyzer with the new SSMS (SQL Server Management Studio). SSMS serves as both the database command line, with which you perform tasks such as creating databases and managing user accounts, and the tool used to write database code. SSIS and Profiler were left out of this merger.
These changes won’t leave users completely satisfied. For starters, doing normal query tasks, such as viewing execution plans and even just running queries, is much slower. Screens can become quite unresponsive while queries run, and it takes much longer to pull up execution plans, sometimes causing the window to seize up. Microsoft also changed the layout of the execution plans, so larger ones are much harder to read. Your favorite keyboard shortcuts have been taken away, and right-clicking on a view -- which used to allow you to edit it -- now takes you directly to query builder. A lot of DBAs won’t like this.