Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources DTS: Microsoft SQL Server 2000 Tool
Print E-mail

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:

dtsrun
[/?] |
[
    [
        /[~]Sserver_name[\instance_name]
        { {/[~]Uuser_name [/[~]Ppassword]} | /E }
    ]
    {    
        {/[~]Npackage_name }
        | {/[~]Gpackage_guid_string}
        | {/[~]Vpackage_version_guid_string}
    }
    [/[~]Mpackage_password]
    [/[~]Ffilename]
    [/[~]R<August 16, 2005bsp; [/Aglobal_variable_name:typeid=value]
    [/Llog_file_name]
    [/WNT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]
]

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.

 

Resources

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
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us

 

 
Bookmark and Share