Creating a DTS package

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:

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.

	1 2  3 4 5 6