Package Execution Utilities

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:

/~S	Server name
/~U	Username
/~P	Password
/~E	Use trusted connection instead of /U /P
/~ /~M	Package password
/~G	Package Guid string
/~V	Package version Guid string
/~F	Structured storage UNC filename (overwritten if /S is also specified)
/~R	Repository database name (uses default if blank; loads package from repository database)

The package operation options that override stored package settings include the following:

/~A	Global variable name:typeid=Value (may quote entire string [including name:typeid])
/~L	Log filename
/~W	Write completion status to Windows Event Log (True or False)

Following is the DTSRun action to perform (the default is to execute the package):

/!X	Do not execute; retrieves package to /F filename
/!D	Do not execute; drop package from SQL Server (cannot drop from storage file)
/!Y	Do not execute; output encrypted command line
/!C	Copies command line to Windows Clipboard (can be used with /!Y and /!X)

Additional DTSRun notes include the following:

~ is optional; if present, the parameter is hex text of encrypted value (0x313233 )

Whitespace between command switch and value is optional

Embedded whitespace in values must be embedded in double quotes

If an option is specified multiple times, the last one wins (except multiple /A)

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.