When data import/export needs are recurring, when exchanges are to another ODBC data sources or flat files, when conversions and transformations are desirable, and when speed is not of paramount importance, DTS tools might be a candidate for you.
DTS packages contain steps that are executed at run time which connect to a data source, copy and, if necessary, transform data, disconnect, and then notify users or other process of condition of completion of the task. A task can be a stored procedure, bulk insert, or the launching of another program. A key ingredient in the DTS package is the control of workflow, allowing you to condition the execution of later tasks to the successful or unsuccessful completion of an earlier one. You can save a DTS task package into Microsoft’s Metadata Services, allowing it to be called at a later time through any products that support DTS.
DTS is extremely easy to use. There is an Import/Export Wizard that can be obtained from the Tools menu of the Enterprise Manager. Then there is the DTS Designer, which is available on the Enterprise Console Tree (under DTS|Local Packages|New Package). The Designer is a completely GUI interface which allows you to build a DTS package by only clicking and dragging icons. Execution of the DTS packages can be started from the same console tree.
In addition to the console, there are two utilities, which are shipped with SQL Server that allow you to run a DTS package from the command prompt. The first is bare bones dtsrun:
The second is its GUI counterpart, dtsrunui, which is also run from the command prompt without any command switches.
The DTS data pump interfaces to import, export and transform data. It is necessary to transform data and to facilitate data driven queries. The data pump is activated by selecting two connections in the Query Designer and right-clicking Transform Data Task, and setting properties on the connection.
This resource provides information on DTS tools.
This resource provides information on DTS package elements.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a free consultation (973) 635 0080
Computer Consultants: Database Development