The dtswiz utility starts the DTS Import/Export Wizard by using command prompt options. It's great for initiating the wizard from within other tools.
The dtsrun utility runs a DTS Package from a command prompt.
Usage: dtsrun /option [value] [/option [value]]
Options ('/?' shows the complete option list; '-' May be substituted for '/'):
The package retrieval options include the following:
The package operation options that override stored package settings include the following:
Following is the DTSRun action to perform (the default is to execute the package):
Additional DTSRun notes include the following:
The DTS Run GUI utility (dtsrunui) allows you to run a DTS Package using a standalone GUI. Just execute dtsrunui from the command prompt and it starts the UI. This facility also has the option to fully manipulate scheduling for the package (see Figure 20.14).
Figure 20.14. DTS Run Package execution and scheduling.
DTS Query Designer is the embedded GUI functionality within DTS Designer that makes it easy to build up queries in a visual manner (see Figure 20.15).
Figure 20.15. DTS Query Designer workbench.
Now, you will look at an actual live data export requirement that is best served by using DTS (as described earlier in this chapter). This is because both ends (source and target) of the data export will be SQL Server 2000 tables and there will be a bit of data transformation along the way.
The requirement is for a small business intelligence data mart (on SQL Server 2000) to be spun off each week from the main OLTP database (also on SQL Server 2000) that addresses a product sales manager's need to see the total year-to-date business that a customer has generated. This requirement has been named "Hot Customers Plus" to indicate the emphasis on customers that are generating ample business for the company. Of course, the data mart is on a separate machine from the critical OLTP system for all of the right reasons. The need will be to spin off this data to the data mart in a total "refresh" scenario after all OLTP processing has concluded on the weekends (see Figure 20.16).
Figure 20.16. Populating a data mart.
Essentially, order data from the OLTP database (contained in Customers, Orders, and Order Details tables) must be aggregated (summed) for every order for each customer. In addition, the total amount to be stored in the YTDBusiness column in the data mart will have to be extended out to reflect the UnitPrice times (*) Quantity calculation during the data transformation. After the data mart is repopulated on the weekend , an e-mail notification must be sent to the primary business user . Although the requirements are many, DTS should be able to handle all of them with no problem.
For the reader to get a good feel for the two main DTS tool capabilities, you will generate the solution to this requirement using the DTS Wizard first, followed by the same solution being generated using DTS Designer.