Execute Package Task objects from VB

[tb]

I have managed to find a way to execute certain tasks within a DTS package through code.
Most of the code examples only shows how to create new tasks and execute the entire package.
This code example will show you how to identify the task names and to execute them.

Hope someone will find this useful.

First of all you need to reference the Microsoft DTSPackage Object Library

Dim objPackage As DTS.Package2
Dim dtsresult As DTS.DTSTaskExecResult
Dim objEvent As Object
Dim objLog As Object
Dim iCount As Integer

'Instanciate the DTS package object
Set objPackage = New DTS.Package2

'Set the server/username/password and the DTS package to be run
objPackage.LoadFromSQLServer "Server IP Address", "UserName", "Password", DTSSQLStgFlag_Default, "", "", "", "DTS_Package_Name"

'Set Global variables
objPackage.GlobalVariables("BooleanValue").Value = True
objPackage.GlobalVariables("StringValue").Value = "This is my string value"
objPackage.GlobalVariables("DateValue").Value = Format(Now, "YYYY/MM/DD")

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'***************************************************
'This will only help you to identify the Task Names and to be taken out
For iCount = 1 To objPackage.Tasks.Count
Debug.Print objPackage.Tasks(iCount).Name
Next iCount
'***************************************************

'Execute the appropriate task
objPackage.Tasks("DTSTask_DTSActiveScriptTask_1").Execute objPackage, objEvent, objLog, dtsresult

objPackage.Tasks("DTSTask_DTSDataPumpTask_1").Execute objPackage, objEvent, objLog, dtsresult

objPackage.UnInitialize

'Mark objects for garbage collection
Set objPackage = Nothing

******************************************************
I found no explanation for the need for the following:
* objEvent
* objLog
BUT it is required parameters and the code works.

Have fun and hope to hear some comments ....


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[lincschris]

This has answered 90% of a question that I had, but can you help with the remaining 10%?

What is the file name for the Microsoft DTSPackage Object Library, as it is not in my reference list and I need to go browsing for it?

Thanks
Chris

 

[tb]

Hi

This is were it resides on my pc
\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll

Hope your remaining 10% is catered for



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[nigelrivett]

Trying to think of a use for this.
If you wanted to make a step executable in isolation why not put it in a separate package then you could include it's dependancies too.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tb]

Hi Nigel

I have a DTS that extracts data from a central server (Great Plains) and copies the data to the project related databases, for applications that are not part of the core business.

Now the use of this to me ...
>Trying to think of a use for this.

I can now manipulate the DTS to only transfer data for a single region/company instead of running the whole DTS. As in some instances a particular region/comapany needs an urgent update and instead of them waiting for say an hour for their update - they can have it within 5 minutes.

As to your second statement .. hope I interpreted it correctly ..
>If you wanted to make a step executable in isolation why not put it in a separate package then you could include it's dependancies too.

Why have several DTS's (in my case it would be between 5 and 10) that cater for the same thing if you can combine this into 1 and manipulate it?

I just saw the power/control that you can have over a DTS. Instead of it being a static object, you can manipulate it to do whatever you want. You can even manipulate the DTS from within ... say an Axtive X script. For me that means greater freedom in the world of DTS'ing.

Hope this all makes sense and that I understood you correctly.


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[nigelrivett]

>> Instead of it being a static object, you can manipulate it to do whatever you want. You can even manipulate the DTS from within ... say an Axtive X script.

Yep I tend to manipulate packages on loading either from within the package or from a loader.

To do what you are doing here though I would probably make this step it's own package - it could be called from the main package for your complete run or in isolation for a single company. Presumably the step has some configuration needed (e.g. company, database, server) - I always do this via global parameters so these would be included in the called package and set either by the main package or whatever calls it in isolation. As you have it you would need to run whatever sets these properties before running the step and would make it more complicated.

The only time I do something similar to what you have is for a temp solution if I want to run a step in a loop and don't have time to test a workflow solution.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Learnin to manipulate properties withing packages is good though. It's stops you thinking you need to copy packages for a slight change (like source database) and enables you to write packages that you can configure for different environments without changing the package.

I've seen companies with hundreds of packages that do very similar things and their release procedure involves someone changing all the connection properties within the packages (and sometimes other things too).

That's why I try to advise people to be cautious about using dts. It tends to be used by people without any experience and ends up in the same mess as other client apps.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tb]

The code example was basically to show people that there is a way to manipulate it ... I needed it and found a way to do it.

Say you are importing files ... this would help a person to change the Connection's DataSource and SourceObject either via code or within the DTS itself (Active X Script) by passing it in as a Global Parameter. (There might be a better way, but unfortunately I don't know how)

>It tends to be used by people without any experience and ends up in the same mess as other client apps.


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[nigelrivett]

>> Say you are importing files ... this would help a person to change the Connection's DataSource and SourceObject either via code or within the DTS itself (Active X Script) by passing it in as a Global Parameter. (There might be a better way, but unfortunately I don't know how)

Executing a step doesn't affect whether you can change the connection properties. This is usually either done in a loader or a dynamic properties task before the step executes (in an ectivex script for v7). For none of these is it necessary to execute the step as the workflow will take care of it.
Have a look at

http://www.nigelrivett.net/SetDTSRunTimeValues.html

http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

This was for v7 and would probably be a dynamic properties task and dtsrun now.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tb]

So if I need to import several files within a specified directory I would need to execute the DTS for each file that exists ...
I would rather pass the path as a Global variable and use the FileSystemObject within an Active X script to read the path and for every file that is found execute the neccessary steps within the package

I just see things from a different angle ...





I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[nigelrivett]

You can use the workflow to execute a loop in a package rather than executing a step - that's the normal way to do it, means you can use the failure workflow.

I would do the imports using bulk insert - simpler, faster and less resource intensive. I almost never use dts for file import or export.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[lincschris]

Thanks tb, that was the last 10% I required - just need to try it out now.