Data Services

The SQL Server 2000 Data Transformation Services (DTS) has been replaced by SSIS which provides three kinds of data services, viz. Extraction, Transformation and Loading. This set of three types of data services is popularly referred to as ETL.

The Process

The summary of the processes involved is data extraction from a source, data transmission to suit application requirements and finally loading of data into a destination. The transformation is the sum process of normalization, sanitization, merger, aggregation and copying of the data. The sources and target locations of the data can be SQL databases, third party ODBC data, flat files or other data locations. SSIS is equipped with efficient tools for designing, testing, executing and monitoring the high-performance ETL services.

New Features of SSIS

In SQL Server 2005 Service Pack 2, SSIS has many new features and enhancements. The major changes are as follows:

1. Connectivity to Access 2007 and Excel 2007 by SQL Server Import and Export Wizard that support Office 12.
2. Interaction with external data sources by the new package is now logged for trouble-shooting.
3. Combo boxes for variables are in the new package supported by data flow components.
4. For string values there is now a ParameterSize property in the Execute SQL task.
5. In the Script Component, the IsNull property of columns warns whenever it is used incorrectly.
6. The final count of cached rows is reported by the Lookup transformation.
7. Service Pack 2 comes with overall improvement in the performance of SSIS which enables faster execution of package and higher data throughput.

Administration of SSIS

The basic service nature of SSIS is administration and management of storing and execution of packages. For administering SSIS, packages are the main consideration. From the viewpoint of Integration Services, the package is like an atomic entity. The package object containing all the objects that preceded it provides SSIS administration of the most fundamental level.