Introduction

If you need to get data into or out of SQL Server, either as a one-off or on a regular basis, there are several ways to achieve this. However in many situations the best solution is to use the excellent DTS (Data Transformation Services) application included with SQL Server 2000 or the replacement SQL Server Integration Services (SSIS) in SQL Server 2005.

DTS/SSIS are large and complex applications, however they are also extremely powerful and consequently we use them for most data conversion tasks, even when neither the source nor the destination is SQL Server!

In addition to transferring data they can also be used to transform or modify the data during the import process. For example a single import file may be used to update multiple tables within the database.

Examples
Examples of some of the tasks that can be undertaken using DTS/SSIS include....
 

Specific Example
One use to which we have employed DTS for was for importing new and updated works orders from a third party computer system. Data from the other computer was placed in .csv files at a predefined location on the network. We wrote a DTS system that :

• Validated the folder structure to ensure that the expected directories were present.
• Checked the validated folder to see if any new files were ready for importing.
• Located the first csv file of Address data to import.
• Validated the incoming raw Address data and imported it into a temporary table.
• Processed all address updates, modifying existing data in the main Address table.
• Processed all additions, adding new records to the same Address table.
• Located the first csv file of Works Order data to import.
• Validated the incoming raw Works Order data and imported it into a temporary table.
• Process all works order updates, modifying existing data in the main Works Order table.
• Process all additions, adding new records to the same Works Order table.
• Re-named the processed files to include the date that they were processed on.
• Moved the re-named csv files to a separate archive directory.
• Cycle back to stage 2 to see if there are any other files to import.
• Create a csv file of updated Works Orders with their revised statuses.
• Create a csv file of Invoices for completed Works Orders.
• Transferred the created csv files using FTP to the client's UNIX server.