SQL Data Transformation

SQL Data transformation services is a set of objects and utilities that allows the automation of extract, transform and load operations to and from SQL databases. The objects are known as SQL data transformation packages and their components are known as SQL data transformation tools.  SQL data transformation services is included with Microsoft SQL Server and is almost always used with SQL Server databases although it can be used with other database types. 

History of SQL Data Transformation

SQL data transformation was introduced with SQL server 7.   Prior to SQL data transformation services database administrators (DBAs) used SQL server Transfer Manager and Bulk Copy Program, included with SQL Server to transfer data.  SQL data transformation services introduced with SQL Server 7 made this process faster and easier.  SQL Data transformation was expanded with SQL Server 2000 but were replaced by SQL Server Integration Service with the SQL 2005 release

Data Transformation Services Packages

The SQL data transformation services package is a logical component of SQL data transformation.  Every SQL data transformation services object is a child component of the package.  Packages are used whenever one modifies data using sql data transformation.  All the metadata about the data transformation is contained in the package.

How SQL Data Transformation Services are Used

SQL data transformation services are used to import and export data from SQL databases.  With SQL data transformation businesses can create maps that will bring data from external sources like flat-files or using ODBC.  The key benefit of SQL data transformation services is that they are packaged with their respective versions of SQL server and can be used by experienced DBAs to bring data into the SQL database.

Alternatives to SQL data transformation

For small organizations especially, using SQL data transformation services can be challenging for several reasons.  SQL data transformation services can be very difficult to use and do not support more advanced file formats like XML.  Alternatives exist to SQL data transformation services through data integration products like EMANIO's Unite! platform.  These products can be easier to use than SQL data transformation and provide greater flexibility when dealing with other database types.