DTS tools Data Transformation Services

1 dts tools

1.1 dts wizards
1.2 dts designer
1.3 dts query designer
1.4 dts run utility

dts tools

dts tools packaged sql server include dts wizards, dts designer, , dts programming interfaces.

dts wizards

the dts wizards can used perform simple or common dts tasks. these include import/export wizard , copy of database wizard. provide simplest method of copying data between ole db data sources. there great deal of functionality not available merely using wizard. however, package created wizard can saved , later altered 1 of other dts tools.

a create publishing wizard available schedule packages run @ times. works if sql server agent running; otherwise package scheduled, not executed.

dts designer

the dts designer graphical tool used build complex dts packages workflows , event-driven logic. dts designer can used edit , customize dts packages created dts wizard.

each connection , task in dts designer shown specific icon. these icons joined precedence constraints, specify order , requirements tasks run. 1 task may run, instance, if task succeeds (or fails). other tasks may run concurrently.

the dts designer has been criticized having unusual quirks , limitations, such inability visually copy , paste multiple tasks @ 1 time. many of these shortcomings have been overcome in sql server integration services, dts s successor.

dts query designer

a graphical tool used build queries in dts.

dts run utility

dts packages can run command line using dtsrun utility.

the utility invoked using following syntax:

dtsrun /s server_name[\instance_name]
{ {/[~]u user_name [/[~]p password]} | /e }
]
{
{/[~]n package_name }
| {/[~]g package_guid_string}
| {/[~]v package_version_guid_string}
}
[/[~]m package_password]
[/[~]f filename]
[/[~]r repository_database_name]
[/a global_variable_name:typeid=value]
[/l log_file_name]
[/w nt_event_log_completion_status]
[/z] [/!x] [/!d] [/!y] [/!c]
]

when passing in parameters mapped global variables, required include typeid. rather difficult find on microsoft site. below typeids used in passing in these values.