Data Transformation Services

<h2 id="history">History</h2>
<p>In SQL Server versions 6.5 and earlier, <a href="/facts/Database_administrators/3SJoq4ma">database administrators</a> (DBAs) used SQL Server Transfer Manager and <a href="/facts/Bulk_Copy_Program/QqzlzXxu">Bulk Copy Program</a>, included with SQL Server, to transfer data. These tools had significant shortcomings, and many[<i>quantify</i>] DBAs used third-party tools such as Pervasive Data Integrator to transfer data more flexibly and easily. With the release of SQL Server 7 in 1998, "Data Transformation Services" was packaged with it to replace all these tools. The concept, design, and implementation of the Data Transformation Services was led by Stewart P. MacLeod (SQL Server Development Group Program Manager), Vij Rajarajan (SQL Server Lead Developer), and Ted Hart (SQL Server Lead Developer).  The goal was to make it easier to import, export, and transform heterogeneous data and simplify the creation of data warehouses from operational data sources.
</p><p>SQL Server 2000 expanded DTS functionality in several ways. It introduced new types of tasks, including the ability to <a href="/facts/File_Transfer_Protocol/THuWgzfw">FTP</a> files, move databases or database components, and add messages into <a href="/facts/Microsoft_Message_Queuing/EhRQKdb9">Microsoft Message Queue</a>. DTS packages can be saved as a Visual Basic file in SQL Server 2000, and this can be expanded to save into any COM-compliant language. Microsoft also integrated packages into <a href="/facts/Windows_2000_security/RMXs3P1w">Windows 2000 security</a> and made DTS tools more user-friendly;  tasks can accept input and output parameters.
</p><p>DTS comes with all editions of SQL Server 7 and 2000, but was superseded by <a href="/facts/SQL_Server_Integration_Services/QqzlzXxu">SQL Server Integration Services</a> in the Microsoft SQL Server 2005 release in 2005.
</p>
<h2 id="dts-packages">DTS packages</h2>
<p>The DTS package is the fundamental logical component of DTS; every DTS object is a <a href="/facts/Information_hiding/dnSDPMaz">child component</a> of the package. Packages are used whenever one modifies data using DTS. All the <a href="/facts/Metadata/qt35z79C">metadata</a> about the data transformation is contained within the package. Packages can be saved directly in a SQL Server, or can be saved in the Microsoft Repository or in <a href="/facts/Component_Object_Model/2DtIAufW">COM</a> files. SQL Server 2000 also allows a programmer to save packages in a <a href="/facts/Visual_Basic/7QMXM6fP">Visual Basic</a> or other language file (when stored to a VB file, the package is actually scripted—that is, a VB script is executed to dynamically create the package objects and its component objects).
</p><p>A package can contain any number of <a href="/facts/ActiveX_Data_Objects/dU8V95sx">connection objects</a>, but does not have to contain any. These allow the package to read data from any <a href="/facts/OLE_DB/YjelpY3o">OLE DB</a>-compliant data source, and can be expanded to handle other sorts of data. The functionality of a package is organized into <i>tasks</i> and <i>steps</i>.
</p><p>A DTS Task is a discrete set of functionalities executed as a single step in a DTS package. Each task defines a work item to be performed as part of the data movement and data transformation process or as a job to be executed.
</p><p>Data Transformation Services supplies a number of tasks that are part of the DTS <a href="/facts/Object_model/sAjjBb7V">object model</a> and that can be accessed graphically through the DTS Designer or accessed programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation and notification situations. For example, the following types of tasks represent some actions that you can perform by using <a href="/facts/Data_transformation_service/LEXCquAh">DTS</a>: executing a single SQL statement, sending an email, and transferring a file with FTP.
</p><p>A step within a DTS package describes the order in which tasks are run and the precedence constraints that describe what to do in the case damage or of failure. These steps can be executed sequentially or in parallel.
</p><p>Packages can also contain <a href="/facts/Global_variable/XWF9zC08">global variables</a> which can be used throughout the package. SQL Server 2000 allows input and output parameters for tasks, greatly expanding the usefulness of global variables. DTS packages can be edited, password protected, scheduled for execution, and retrieved by version.
</p>
<h2 id="dts-tools">DTS tools</h2>
<p>DTS tools packaged with SQL Server include the DTS wizards, DTS Designer, and DTS Programming Interfaces.
</p>
<h3>DTS wizards</h3>
<p>The DTS <a href="/facts/Wizard_(software)/8c4Y3BQ0">wizards</a> can be used to perform simple or common DTS tasks. These include the <i>Import/Export Wizard</i> and the <i>Copy of Database Wizard</i>. They provide the simplest method of copying data between <a href="/facts/OLE_DB/YjelpY3o">OLE DB</a> data sources. There is a great deal of functionality that is not available by merely using a wizard. However, a package created with a wizard can be saved and later altered with one of the other DTS tools.
</p><p>A <i>Create Publishing Wizard</i> is also available to schedule packages to run at certain times. This only works if <a href="/facts/SQL_Server_Agent/JK1nYlyN">SQL Server Agent</a> is running; otherwise the package will be scheduled, but will not be executed.
</p>
<h3>DTS Designer</h3>
<p>The DTS Designer is a <a href="/facts/Graphical_tool/wBlQFWDP">graphical tool</a> used to build complex DTS Packages with workflows and event-driven logic. DTS Designer can also be used to edit and customize DTS Packages created with the DTS wizard.
</p><p>Each connection and task in DTS Designer is shown with a specific <a href="/facts/Icon_(computing)/habCR8KJ">icon</a>. These icons are joined with precedence constraints, which specify the order and requirements for tasks to be run. One task may run, for instance, only if another task succeeds (or fails). Other tasks may run concurrently.
</p><p>The DTS Designer has been criticized for having unusual quirks and limitations, such as the inability to visually <a href="/facts/Copy_and_paste/eksLoJ9M">copy and paste</a> multiple tasks at one time. Many of these shortcomings have been overcome in <a href="/facts/SQL_Server_Integration_Services/QqzlzXxu">SQL Server Integration Services</a>, DTS's successor.
</p>
<h3>DTS Query Designer</h3>
<p>A graphical tool used to build <a href="/facts/Information_retrieval/MFSmpeNw">queries</a> in DTS.
</p>
<h3>DTS Run Utility</h3>
<p>DTS Packages can be run from the command line using the DTSRUN Utility.
The utility is invoked using the following  syntax: 
</p>
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]
]

<p>When passing in parameters which are mapped to Global Variables, you are required to include the typeid. This is rather difficult to find on the Microsoft site. Below are the TypeIds used in passing in these values.
</p>
<table><tbody><tr><th>Type</th><th>typeid</th></tr><tr><td>Boolean</td><td>11</td></tr><tr><td>Currency</td><td>6</td></tr><tr><td>Date</td><td>7</td></tr><tr><td>Decimal</td><td>14</td></tr><tr><td>HRESULT</td><td>25</td></tr><tr><td>Int</td><td>22</td></tr><tr><td>Integer (1-byte)</td><td>16</td></tr><tr><td>Integer (8-byte)</td><td>20</td></tr><tr><td>Integer (small)</td><td>2</td></tr><tr><td>LPWSTR</td><td>31</td></tr><tr><td>Pointer</td><td>26</td></tr><tr><td>Real (4-byte)</td><td>4</td></tr><tr><td>Real (8-byte)</td><td>5</td></tr><tr><td>String</td><td>8</td></tr><tr><td>Unsigned int (1-byte)</td><td>17</td></tr><tr><td>Unsigned int (2-byte)</td><td>18</td></tr><tr><td>Unsigned int (4-byte)</td><td>19</td></tr><tr><td>Unsigned int (1-byte)</td><td>21</td></tr><tr><td>Unsigned int</td><td>23</td></tr></tbody></table>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/OLAP/VNNbIlD0">OLAP</a></li>
<li><a href="/facts/Data_warehouse/we5rblq9">Data warehouse</a></li>
<li><a href="/facts/Data_mining/AH53q5ac">Data mining</a></li>
<li><a href="/facts/SQL_Server_Integration_Services/QqzlzXxu">SQL Server Integration Services</a></li>
<li><a href="/facts/Meta_Data_Services/eJkVbpNh">Meta Data Services</a></li></ul>

<ul><li>Chaffin, Mark; Knight, Brian; Robinson, Todd (2003). <i>Professional SQL Server 2000 DTS</i>. <a href="/facts/Wrox_Press/4pfQvkJF">Wrox Press</a> (Wiley Publishing, Inc.). <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-7645-4368-7.</li></ul>
<h2 id="external-links">External links</h2>
<ul><li><a href="http://msdn2.microsoft.com/en-us/library/aa933484(SQL.80).aspx">Microsoft SQL Server: Data Transformation Services (DTS)</a></li>
<li><a href="http://www.sqldts.com/">SQL DTS unique DTS information resource</a> <a href="https://web.archive.org/web/20210509112657/http://sqldts.com/">Archived</a> 2021-05-09 at the <a href="/facts/Wayback_Machine/nmQ3a6JC">Wayback Machine</a></li>
<li><a href="http://support.microsoft.com/kb/238912">Understanding Microsoft Repository</a></li>
<li><a href="https://web.archive.org/web/20120902234344/http://pragmaticworks.com/Resources/Webinars/Default.aspx">DTS Videos & Training</a></li></ul>

Data Transformation Services open-in-new

Data Transformation Services