Home arrow ASP arrow Page 3 - Creating DTS Packages With SQL Server 2000

Creating DTS Packages With SQL Server 2000

Data Transformation Services (DTS) were added to SQL Server 7 and allow us to combine several data-related tasks into one common object. In this article Tim shows us how to create a DTS package with SQL Server 2000 that will access a database and email the results of a query to some sales executives. He also shows us how to execute and error trap DTS packages from within an ASP script.

Author Info:
By: Tim Pabst
Rating: 5 stars5 stars5 stars5 stars5 stars / 327
February 01, 2002
  1. · Creating DTS Packages With SQL Server 2000
  2. · What is a DTS package?
  3. · Creating a DTS package
  4. · Exporting the results set to a text file
  5. · Executing our DTS package in ASP
  6. · Conclusion

print this article

Creating DTS Packages With SQL Server 2000 - Creating a DTS package
(Page 3 of 6 )

Let's now create a DTS package that will do the following:
  • Query the pubs database for a list of sales data
  • Save the results of this query to a flat text file
  • Attach this text file to an email and send it to some sales executives
Start by loading the DTS designer (as described earlier). Click on the  icon to add a Microsoft OLEDB Provider for SQL Server connection object to our DTS package. On the connection properties page, simply change the database to pubs and click the OK button.

Next, click on the  icon to add an execute SQL task to our DTS package. An execute SQL task allows us to query a database and use the results set in our package. You'll notice that when you click on the execute SQL task icon, its properties page will appear. Enter the following code into the SQL statement text box:

SELECT sa.stor_id, sa.qty

FROM dbo.sales sa

INNER JOIN stores st

ON sa.stor_id = st.stor_id

WHERE sa.payterms = ?

As you can see, we're returning the results of an inner-join query, which merges results from two tables based on a common similarity. In our example, we are using the sales and stores tables of the pubs database that is included with every SQL Server 2000 installation. We are returning all records whose payterms field matches a specific value, "?". The question mark in the where clause tells DTS that we are referring to a global input parameter that we have defined elsewhere.

The DTS designer allows us to use COM objects to instantiate and run a DTS package. Through these COM objects, we can define global-level variables that can be accessed internally by our DTS package. In our example above, the "?" in the where clause would be the value of the payterms field that we want to filter the results set on.

We can define this input parameter by clicking on the parameters button of the execute SQL task property page. The parameter-mapping page appears. Click on the "Create Global Variables..." button. You'll notice a list containing three fields: name, type, and value. Enter "gPaymentTerm" in the name field. Our global variable will be a string type, and have a default value of "Net 60". Click on the OK button to create our new global input variable. This will bring you back to the parameter-mapping page.

The parameters listed on this page tell DTS the names of the variables that it should expect us to pass in when we attempt to execute our DTS package. Choose the gPaymentTerm from the "Input Global Variables" drop-down box:

Telling DTS that we expect an input parameter named gPaymentTerm

Click OK. We now want to create an output parameter variable, which will hold the results set of our SQL query. Click on the "Output Parameters" tab and then on the rowset radio button. Click on the "Create Global Variables" button. Enter gResults as the variable name and choose the "<other>" variable type. Click OK and then select gResults from the drop-down list that appears next to the radio button. Click OK then OK to exit all open property pages.

By creating the output parameter above, we've just told DTS that we want to capture the entire result set of our SQL query into a variable, which we can then return to another task for processing. This is a powerful feature of DTS packages, and we will look more at it in the next section.
blog comments powered by Disqus

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials