- DTS Overview
- DTS Packages
- Developing DTS Packages
- References
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
DTS Packages
A DTS package is a collection of programmable objects that work together to accomplish a specific goal. You can use a DTS package to perform a variety of tasks:
Import and export data. A DTS package can import or export data to any OLE database or ODBC-compliant data source. Microsoft SQL Server 2000 includes drivers to connect to SQL Server, Oracle, Access, Excel, Visual FoxPro, dBASE, Paradox, HTML, text files, Microsoft Exchange server, Microsoft Active Directory, and Microsoft Analysis Services.
Transform data. Execute ActiveX scripts or scripts written in JScript, VBScript, or PerlScript.
Copy SQL Server objects. Copy data, indexes, triggers, and so on among two or more SQL Server databases.
Send and receive messages. Send messages from a DTS package to users or to other DTS packages.
Execute T-SQL commands. Automate the execution of T-SQL commands with DTS packages.
A DTS package contains a series of steps (tasks) that the package must complete. These tasks can be executed in sequence or parallel with one another. For example, if you want to copy data between two tables, you must first check that the destination table exists before copying data. If the table doesn't exist, the DTS package must create it before copying the data to it. The DTS package must execute these steps in sequence. If you want to copy data among several tables, you can run several parallel sequences to save time.
To control package workflow, the DTS package uses the concept of precedent constraints. A precedent constraint establishes rules that the DTS package must follow every time a step is completed. There are three precedent constraints:
On success. A step executes only when the previous step is completed successfully.
On failure. A step executes only when the previous step has failed.
On completion. A step executes when the previous step is completed, regardless of the result.
Figure 1 shows how to use precedent constraints in a DTS package. This package executes two T-SQL queries at the same time. The green arrows indicate a precedent constraint of On success; this means that the third T-SQL query in step 3 executes only if the previous two queries are completed successfully. The last step is the execution of an ActiveX script that will occur only if the three T-SQL queries have been completed successfully.
)
Figure
1 Precedent constraints.
SQL Server 2000 DTS packages can be stored to SQL Server as a database object, as SQL Server 2000 Metadata Services, as a structured storage file that's saved to any directory on the server or client computer, or as a Microsoft Visual Basic file that can be used to incorporate packages into Visual Basic applications.