Professional SQL Server™ 2005 Programming

2.5. SQL Server Integration Services (SSIS)

Your friend and mine — that's what SSIS (formerly known as Data Transformation Services — or DTS) is. I simply sit back in amazement every time I look at this feature of SQL Server. With SSIS, a tremendous amount of the coding (usually in some client-side language) that had to be done to perform complex data extracts or imports. SSIS allows you to take data from any data source that has an OLE DB or .NET data provider and pump it into a SQL Server table.

While transferring our data, we can also apply what are referred to as transformations to that data. Transformations essentially alter the data according to some logical rule(s). The alteration can be as simple as changing a column name, or as complex as an analysis of the integrity of the data and application of rules to change it if necessary. To think about how this is applied, consider the problem of taking data from a field that allows nulls and moving it to a table that does not allow nulls. With SSIS, you can automatically change out any null values to some other value you choose during the transfer process (for a number, that might be zero, or, for a character, it might be something like "unknown").