como hacer un dts

8/3/2019 Como Hacer Un DTS

1/13

ASP

Creating DTS Packages With SQL Server 2000

Contributed by Tim Pabst

20020201

[ Send Me Similar Content When Posted]

[ Add DeveloperShed Headlines To Your Site]

DISCUSS NEWS SEND PRINT PDF

Article Index: Data Transformation Services (DTS) were added to SQL Server 7 and allow us to combine

several datarelated tasks into one common object. In this article Tim shows us how tocreate 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.Back in the days when Microsoft SQL Server 6.5 was

still hot property, the bulk copy program was the only way to backup or export databases

from SQL Server to other data sources, such as other database servers or text files. DBA's

(Database Administrators) would spend hours setting up and automating backup scripts for

each server, and it wasn't until the release of SQL Server 7 that Microsoft finally gave DBA's

a set of drag and drop tools to accomplish this more quickly and easily. This set of tools is

known as DTS (Data Transformation Services).

By creating DTS packages, we can combine several tasks into one process and use any

programming language that supports automation (such as Visual Basic 6, ASP, or C++) toexecute these packages and monitor their progress for errors, etc.

In this article I'm going to show you how to create a DTS package that retrieves sales data

from a Microsoft SQL Server 2000 database and emails it to a fictitious list of company

executives. We will take an indepth look at creating the package, what tools we can use to

make our job easier, and the details of the DTS.Package COM object, which we can use to

execute a DTS package. We will also look at calling a DTS package from ASP.

To test the examples in this article, you should have access to a Windows 2000 web server

running IIS version 5 (configured with an SMTP mail server), as well as SQL Server 2000.

You should also be familiar with VBScript, ASP, and some basic SQL "INNER JOIN"syntax. A DTS package is a set of related objects (connections, tasks, and workflows) that

can be used to access, transform and manipulate data from a wide range of sources including

text files and relational databases. DTS was introduced with the release of SQL Server 7.0

and was carried across to SQL Server 2000 because of its immense popularity.

To create DTS packages, we use the DTS designer (which is accessible through enterprise

manager). We will talk more about the DTS designer in a minute, but for now all we need to

know is that there are two types of objects that it can create: connections and tasks.

Dev Articles 07/15/2005 12:49:18 PM

1/13
http://www.developershed.com/devalert.phphttp://www.developershed.com/devtext.phphttp://confirmed%20empty%2020031125rvv/http://www.developershed.com/newsletter.phphttp://index2.php/?option=content&task=emailform&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=no,titlebar=no,menubar=no,resizable=yes,width=400,height=200,directories=no,location=no%27);http://index2.php/?option=content&task=view&id=64&pop=1&hide_ads=1&page=0&hide_js=1%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&do_pdf=1&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&do_pdf=1&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&do_pdf=1&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&task=view&id=64&pop=1&hide_ads=1&page=0&hide_js=1%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&task=view&id=64&pop=1&hide_ads=1&page=0&hide_js=1%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no%27);http://index2.php/?option=content&task=emailform&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=no,titlebar=no,menubar=no,resizable=yes,width=400,height=200,directories=no,location=no%27);http://index2.php/?option=content&task=emailform&id=64%27,%20%27win2%27,%20%27status=no,toolbar=no,scrollbars=no,titlebar=no,menubar=no,resizable=yes,width=400,height=200,directories=no,location=no%27);http://www.developershed.com/newsletter.phphttp://www.developershed.com/newsletter.phphttp://confirmed%20empty%2020031125rvv/http://confirmed%20empty%2020031125rvv/http://www.developershed.com/devtext.phphttp://www.developershed.com/devalert.phphttp://www.devarticles.com/cp/bio/Tim-Pabst/http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/


2/13

A connection object represents a connection to a data store, such as SQL Server 2000, an

Oracle database, or even a text file. They are used to give tasks access to the data they need

to transform or manipulate. A task object allows us to work with data accessed through

connection objects. Tasks allow us to copy, query, or manipulate data and are generally

responsible for providing any sort of activity in a DTS package.

The DTS designer allows us to do more than just access and manipulate data, however. It

includes a set of tasks that allow us to transfer files using FTP, send messages to an MSMQ

queue, or even create our own ActiveX scripting tasks using VBScript, JScript or PerlScript.

By combining all of the connections and tasks that are made available to us through the DTS

designer, we can create some fairly complex datarelated packages.

Let's take a look at the DTS designer right now. Load Enterprise Manager (Start >

Programs > Microsoft SQL Server > Enterprise Manager) and expand both the Microsoft

SQL Servers and SQL Server Group nodes in the left column. Next, expand your database

node as well as that databases Data Transformation Services node. Enterprise Manager

should look something like this:

Right click on the Local Packages leaf and choose the New Package option. This will load

the DTS package designer. Notice the two sets of objects down the left side of the toolbar?

These are the connection and task objects that we will use to create our sample DTS

package. The huge area of white space on the right is where the objects that we create as part

of our package will go. Click on the icon for any connection or task object to add it to our

currently unsaved DTS project. Notice how the object appears in the white space on the

right, like this:

Dev Articles 07/15/2005 12:49:18 PM

2/13


3/13

Take a look at the options in the menu bar across the top of the DTS designer window. The

package menu allows us to save our packages and modify their properties amongst other

things. The connection and task menus display exactly the same icons as in the left pane, but

they also include descriptions.

The last menu is the workflow menu. The workflow menu contains three options: On

Completion, On Success, and On Failure. Each object in our DTS package can respond tothese events (which represent the completion of a task, the successful completion of a task,

and the unsuccessful completion of a task respectively), and we can optionally use

relationships to link our objects to tasks that should be executed when SQL Server fires these

events.

Before we move onto creating our sample DTS package, take a look at some of the more

important connections and tasks that are available to us through the DTS designer:

Connections:

Microsoft OLEDB Provider for SQL Server: Allows us to define aconnection to any OLEDB compatible data source such as SQL Server 2000,

Access, Paradox, dBase or MySQL.

Microsoft Access: Allows us to define a connection to a Microsoft Access

database.

Microsoft Excel: Allows us to define a connection to a Microsoft Excel

database.

Text File: Allows us to define a connection (either source/destination) to an

ASCII text file. Useful when using commaseparated valued flatfile tables.

Dev Articles 07/15/2005 12:49:18 PM

3/13


4/13

Microsoft Data Link: Allows us to define a connection to a data source via a

UDL (Universal Data Link) file.

Tasks:

ActiveX Script Task: Allows us to use an ActiveX scripting language to

interact with other tasks, instantiate COM objects, etc.

Transform Data Task: Allows us to define sourceanddestination

relationships between connections.

Execute SQL Task: Allows us to build a query against the data source of a

connection object. We can then manipulate the results of this query as a recordset

through an ActiveX scripting task object.

File Transfer Protocol Task: Allows us to setup a realtime FTP connection to

a remote host and work with the files on that host.

Copy SQL Server Objects: Allows us to copy data from one source to another.

Now that we know what DTS packages are and have a good understanding of the types of

objects we can use to create them, let's create a simple package of our own. 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 innerjoin 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.

Dev Articles 07/15/2005 12:49:18 PM

4/13


5/13

The DTS designer allows us to use COM objects to instantiate and run a DTS package.

Through these COM objects, we can define globallevel 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 parametermapping 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 parametermapping 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" dropdown 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 "" variable type. Click OK and then select gResults from the

dropdown list that appears next to the radio button. Click OK then OK to exit all open

property pages.

Dev Articles 07/15/2005 12:49:18 PM

5/13


6/13

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. Click on the icon to add an ActiveX scripting task to our DTS package.

When its properties page appears, you'll notice that we have a text box containing some

strange looking VBScript:

The Main() function is executed whenever a call to our new ActiveX script task takes place.

Inside this function, we can instantiate new COM objects using the CreateObject() method,

work with variables, or do anything else that VBScript is capable of. Enter the following

code for the Main function:

'*****************************************

' Visual Basic ActiveX Script

'*****************************************

Function Main()

On Error Resume Next

Dev Articles 07/15/2005 12:49:18 PM

6/13


7/13

dim strRecord

dim strEmailBody

dim objFSO

dim objStream

dim objMail

dim objResults

const OUTPUT_FILE = "c:\salesreport.txt"

const EXECUTIVE_EMAILS = "[email protected];[email protected]"

const fsoForWriting = 2

set objFSO = CreateObject("Scripting.FileSystemObject")

set objMail = CreateObject("CDONTS.NewMail")

set objResults = DTSGlobalVariables("gResults").Value

set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)

'Loop through the records and output each one

'to a file.

while not objResults.EOF

strRecord = "Store #" &objResults.Fields(0).value &_

"sold " &objResults.Fields(1).value &_

"items with payment type " &DTSGlobalVariables("gPaymentTerm").Value

objStream.WriteLine(strRecord)

objStream.WriteBlankLines(1)

objResults.MoveNext

wend

'Create the body of the email

strEmailBody = "Good morning," &vbCrLf &_

"Please find attached this months sales " &_

Dev Articles 07/15/2005 12:49:18 PM

7/13


8/13

"reports. If there are any problems, then " &_

"please email the sales department."

'Attach the file to an email and send it

objMail.To = EXECUTIVE_EMAILS

objMail.Subject = "Sales Report"

objMail.Importance = 2 'High

objMail.AttachFile OUTPUT_FILE

objMail.Subject = strEmailBody

objMail.Send

if err.count = 0 then

Main = DTSTaskExecResult_Success

else

Main = DTSTaskExecResult_Failure

end if

End Function

To keep this article simple, I won't go into too much detail about the code inside of the

Main() function. As you can see, however, we instantiate a new email message and

FileSystemObject that allow us to write to files. Remember how we defined a global output

variable earlier? This variable represents our record set, and in our code we use the following

line to get the results of our query into a recordset object:

set objResults = DTSGlobalVariables("gResults").Value

The DTSGlobalVariables collection contains the input and output parameters that we have

defined for our DTS package. All global variables are listed on the ActiveX script tasks

property page in a list down the left:

Dev Articles 07/15/2005 12:49:18 PM

8/13


9/13

We can add a reference to any of these global variables by simply double clicking on them.

Once the VBScript code has extracted each record from the record set and wrote them into

the c:\salesreport.txt file, we use a new CDO.NewMail object to attach that file to an email

and send it to some "executives" email address (in this example it's me and Mitchell) using

our IIS SMTP mail server.

Our ActiveX scripting task must return a value indicating whether it succeeded or failed. We

have two possible choices: DTSTaskExecResult_Success, or DTSTaskExecResult_Failure.

In our example we have used the err.count value to determine if any errors occurred. If they

didn't, we return DTSTaskExecResult_Success.

On the other hand, if an error did occur, we return DTSTaskExecResult_Failure. There's one

important thing I should mention here: Normally, if you caught an error in an ActiveX script

task, you would fire off another scripting task to handle that error, maybe writing to a log

file, etc. To keep our example simple however, we will simply return

DTSTaskExecResult_Failure, indicating that our script task failed somewhere.

That covers actually creating our DTS package. Click OK to close all open property pages.

The DTS designer will ask if we want to save our package. Enter "SalesPkg" as the package

name and click the OK button. In Enterprise Manager you should see our new DTS package

under the Local Packages leaf of the Data Transformation Services node of the pubs

database:

Dev Articles 07/15/2005 12:49:18 PM

9/13


10/13

If you right click on our new DTS package, you can go back into the DTS designer, execute

it, or even schedule its execution. Let's now look at how we can use ASP script and some

simple COM object instantiation to execute our DTS package programmatically. Thanks to

the way everything in Windows is tightly integrated, we only need a couple of lines to

execute our DTS package. We need to instantiate a new DTS package object. The DTS

package object is a COM object with the ProgID of "DTS.Package". Once we have

instantiated a new DTS package object, we can passin our global variable (for the WHERE

clause), and execute each step in our DTS package sequentially.

Create a new ASP script called "testdtspkg.asp" and enter the following code into it:

Sales Report DTS Package


11/13

dim blnSucceeded

const DTSSQLStgFlag_Default = 0

const DTSStepExecResult_Failure = 1

set objDTSPackage = Server.CreateObject("DTS.Package")

blnSucceeded = true

objDTSPackage.LoadFromSQLServer "(local)", "sa", "", DTSSQLStgFlag_Default, "", "",

"", "SalesPkg"

objDTSPackage.GlobalVariables("gPaymentTerm").Value = "Net 30"

objDTSPackage.Execute

for each objDTSStep in objDTSPackage.Steps

if objDTSStep.ExecutionResult = DTSStepExecResult_Failure then

strResult = strResult &"Package " &objDTSStep.Name &" failed.
"

blnSucceeded = false

else

strResult = strResult &"Package " &objDTSStep.Name &" succeeded.
"

end if

next

if blnSucceeded then

Response.Write "Package Succeeded"

else

Response.Write "Package Failed"

end if

Response.Write strResult

%>

Dev Articles 07/15/2005 12:49:18 PM

11/13


12/13

The DTS.Package object has a LoadFromSQLServer method that lets us load our DTS

package. It's signature looks like this:

LoadFromSQLServer ServerName, Username, Password, Flags, PackagePassword,

PackageGUID, PackageVersionGUID, Package Name, PersistsHost

As you can see in our example above, several of the parameters are optional. Once we have

loaded our package, we have to assign a value to its global variable, gPaymentTerm.

Remember that this global variable will replace the "?" in the WHERE clause of our query to

the sales and stores tables of the pubs database? We use the GlobalVariables collection to do

this:

objDTSPackage.GlobalVariables("gPaymentTerm").Value = "Net 30"

Next, we use the Steps collection of our DTS package object to execute each step in our DTS

package sequentially. First step is the OLEDB connection, then the execute SQL task, andlastly the ActiveX script task. For each step, we check whether it succeeded/failed using its

ExecutionResult variable and note this in the strResult string variable. If a step fails, then the

blnSucceeded variable is set to false.

Lastly, we output whether or not our DTS package succeeded/failed, as well as the details of

each step that it contains. When I ran the script in my browser, it gave me the following

results:

Notice that only the tasks are listed here, and not the actual connection object. In our

example, the sales email was sent to [email protected] and [email protected].

Here's a snippet from the email attachment (c:\salesreport.txt):

Store #7066 sold 50 items with payment type Net 30

Dev Articles 07/15/2005 12:49:18 PM

12/13


13/13

Store #7067 sold 40 items with payment type Net 30

Store #7067 sold 20 items with payment type Net 30 As you can see from the sample in this

article, DTS packages are a quick and easy way to create reusable objects to work with data.

I am currently working on a banking project, and we use DTS packages to distribute sales

data to six financial processing servers, each running SQL Server 2000 and BizTalk.

DTS is one of the many features of SQL Server that can be used to create a professional set

of objects to make your everyday workings with data easier and more streamlined. Just like

databases, DTS packages can also be exported, so they can be shared amongst multiple

servers.

If you've never used DTS packages before, then I'd encourage you to take some time playing

around with all of the connection and task objects in the DTS designer. They're a bit tricky to

figure out at first, but you should be able to come to grips with them quickly using this

article and the links/books shown below.

Dev Articles 07/15/2005 12:49:18 PM

13/13

como hacer un dts

Documents