| SSIS includes a complete set of data transformation and integration services, graphical tools, programmable objects, and APIs. You can use SSIS to perform tasks such as integrating data from varied data stores, cleansing data, refreshing data into business intelligence data warehouses and data marts, automating administrative functions, and so on. Some of this functionality was available with DTS in SQL Server 2000. The current release takes the DTS functionality to the next level by introducing the following new features: Architectural enhancements The new SSIS architecture is divided into four core components: clients, the SSIS service, the runtime engine, and the data flow engine. The SSIS client component includes the graphical and command-line tools and wizards that can be used for package design and creation. The SSIS service is responsible for managing the storage of packages and tracking packages that are running. The SSIS runtime engine manages and sets appropriate properties during runtime and provides support for logging and debugging. The SSIS data flow engine controls the flow of data from source to destination. The SSIS data flow components include source adapters, transformations, and destination adapters. The separation of the different components to be managed by different services allows for more manageability and efficiency. The new architecture allows you to extract data from multiple sources and write data to multiple destinations. Performance enhancements The new SSIS architecture uses in-memory buffers as much as possible to move data from the source to the destination. SSIS is designed to leverage the increased amount of the memory. For example, in the case of a table lookup task, the lookups are stored in the memory cache to avoid expensive trips to the disk. This results in improved performance of the task execution. New transformations and tasks SSIS introduces several new tasks that aid in data cleansing and transformation. Examples of these new tasks include Derived Column, Data Conversion, File Extractor, and File Inserter. In addition, different tasks such as Multicast, Conditional Split, Sort, Aggregation, Merge, and Merge Join have been introduced, and they can be very useful for redirecting output based on defined conditions and to perform various functions on the data. The new tasks specific to Analysis Services and data mining include Slowly Changing Dimensions, Data Mining Query, and so on. The new looping containers, such as the For Loop and Foreach Loop, allow you to iterate over files in a folder iterate over an items list, and so on. The new Script tasks allow you to use .NET languages such as C# or Visual Basic .NET to author scripts. SSIS designer The new graphical designer includes better tools and templates to enable easy creation of packages. The designer offers better project management, new tasks and templates for easier package creation, tools for better deployment, and storage and debugging tools to monitor package execution. The designer includes Package Explorer, which allows you to browse and access different features of the package; Solution Explorer, to access projects and their associated packages; and different debugging windows, such as output windows, breakpoints, watches, and so on. The designer separates control flow, data flow, and event handling onto multiple tabs/screens, allowing complete control and flexibility to author complex packages and to provide better control flow semantics. Extensibility If none of the built-in components satisfy your needs, you can create your own custom source, transformation, enumerators, and other control flow and data flow elements by using the SSIS extensibility object model and any .NET programming language, such as Visual Basic .NET or C#. Import/Export Wizard enhancement The mport/Export Wizard has been enhanced to include better support for data in flat files, including the ability to manipulate data at the column level. The wizard allows you to create a new database if the destination database does not exist and allows you to map the source and destination columns. You can even omit the columns that are not required. In addition, the wizard allows you to preview the data in real time.
Let's now take a closer look at the new SSIS architecture. |
