Run a DTS package from another DTS package

Run a DTS package from another DTS package

Post by Kanik » Tue, 10 Aug 1999 04:00:00



Hi,

I have a DTS package which runs the main loop. It has to call another DTS
package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Euan Garde » Tue, 10 Aug 1999 04:00:00


You can either use ActiveX Script to run the package or use the
ExecuteProcess
Task to call DTSRun and execute the other package.

-Euan

Quote:> Hi,

> I have a DTS package which runs the main loop. It has to call another DTS
> package from within the loop. How can i achieve this?


 
 
 

Run a DTS package from another DTS package

Post by Kanik » Tue, 10 Aug 1999 04:00:00


How do i run a DTS package from another package's ActiveX script? Is there a
'Run' command. I though of using the Execute method, but at this stage am
not able to figure out how to get the Package handle to do this. (The
examples given in Books on-line show how to get the handle of the current
package.)

>You can either use ActiveX Script to run the package or use the
>ExecuteProcess
>Task to call DTSRun and execute the other package.

>-Euan


>> Hi,

>> I have a DTS package which runs the main loop. It has to call another DTS
>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Tom Morea » Tue, 10 Aug 1999 04:00:00


Use DTSGlobalVariables.Parent to get the package handle, e.g.

Dim pkg

Set pkg = DTSGlobalVariables.Parent
pkg.LoadFromSQLServer ...
pkg.Execute

    Tom

---

How do i run a DTS package from another package's ActiveX script? Is there a
'Run' command. I though of using the Execute method, but at this stage am
not able to figure out how to get the Package handle to do this. (The
examples given in Books on-line show how to get the handle of the current
package.)


>You can either use ActiveX Script to run the package or use the
>ExecuteProcess
>Task to call DTSRun and execute the other package.

>-Euan


>> Hi,

>> I have a DTS package which runs the main loop. It has to call another DTS
>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Kanik » Wed, 11 Aug 1999 04:00:00


Thanx Tom.

>Use DTSGlobalVariables.Parent to get the package handle, e.g.

>Dim pkg

>Set pkg = DTSGlobalVariables.Parent
>pkg.LoadFromSQLServer ...
>pkg.Execute

>    Tom

>---


>How do i run a DTS package from another package's ActiveX script? Is there
a
>'Run' command. I though of using the Execute method, but at this stage am
>not able to figure out how to get the Package handle to do this. (The
>examples given in Books on-line show how to get the handle of the current
>package.)


>>You can either use ActiveX Script to run the package or use the
>>ExecuteProcess
>>Task to call DTSRun and execute the other package.

>>-Euan


>>> Hi,

>>> I have a DTS package which runs the main loop. It has to call another
DTS
>>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Kanik » Wed, 11 Aug 1999 04:00:00


When i ran this :

Function Main()
Dim pkg
Set pkg = DTSGlobalVariables.Parent
pkg.LoadFromSqlServer (local),"sa",,,,,,"pkg"
pkg.Execute
End Function

pkg is the DTS package executed.

It does not stop running. Goes into some kind of an infinite execution. I
then have to start the NT task manager and delete the task. I tried
pkg.Uninitialize(), but it didn't help.


>Use DTSGlobalVariables.Parent to get the package handle, e.g.

>Dim pkg

>Set pkg = DTSGlobalVariables.Parent
>pkg.LoadFromSQLServer ...
>pkg.Execute

>    Tom

>---


>How do i run a DTS package from another package's ActiveX script? Is there
a
>'Run' command. I though of using the Execute method, but at this stage am
>not able to figure out how to get the Package handle to do this. (The
>examples given in Books on-line show how to get the handle of the current
>package.)


>>You can either use ActiveX Script to run the package or use the
>>ExecuteProcess
>>Task to call DTSRun and execute the other package.

>>-Euan


>>> Hi,

>>> I have a DTS package which runs the main loop. It has to call another
DTS
>>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Bill Hodghea » Wed, 11 Aug 1999 04:00:00


The ActiveX script must return a value.
Also, beware of executing the package you are currently running again.. you
can create a loop this way.

> When i ran this :

> Function Main()
> Dim pkg
> Set pkg = DTSGlobalVariables.Parent
> pkg.LoadFromSqlServer (local),"sa",,,,,,"pkg"
> pkg.Execute
> End Function

> pkg is the DTS package executed.

> It does not stop running. Goes into some kind of an infinite execution. I
> then have to start the NT task manager and delete the task. I tried
> pkg.Uninitialize(), but it didn't help.


> >Use DTSGlobalVariables.Parent to get the package handle, e.g.

> >Dim pkg

> >Set pkg = DTSGlobalVariables.Parent
> >pkg.LoadFromSQLServer ...
> >pkg.Execute

> >    Tom

> >---


> >How do i run a DTS package from another package's ActiveX script? Is
there
> a
> >'Run' command. I though of using the Execute method, but at this stage am
> >not able to figure out how to get the Package handle to do this. (The
> >examples given in Books on-line show how to get the handle of the current
> >package.)


> >>You can either use ActiveX Script to run the package or use the
> >>ExecuteProcess
> >>Task to call DTSRun and execute the other package.

> >>-Euan


> >>> Hi,

> >>> I have a DTS package which runs the main loop. It has to call another
> DTS
> >>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Kanik » Wed, 11 Aug 1999 04:00:00


The ActiveX used is as follows :

****
Function Main()
Dim fso, fldr,fc, f1,s,copypath, pkg,MyFile
copypath="r:\eisdesign\copyfile\TempDataFile.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("r:\eisdesign\datafiles")
Set fc=fldr.Files
For Each f1 in fc
    f1.Copy copypath
    Set pkg = DTSGlobalVariables.Parent
    pkg.LoadFromSQLServer (local),"sa",,,,,,"ImpData"
    pkg.Execute
Next
Set fso=Nothing
Main = DTSTaskExecResult_Success
End Function
****

The ImpData package tranfers data from a txt file to a SQL Server table.
This package successfully performs the data transfer but the dialog box
which comes up while executing the package, remains and then finally i have
to delete the task from the Task Manager.
Also is there a way to dynamically change the Source File of ImpData
package. Currently i am copying each file to TemDataFile.txt and executing
the DTS Package.


>The ActiveX script must return a value.
>Also, beware of executing the package you are currently running again.. you
>can create a loop this way.


>> When i ran this :

>> Function Main()
>> Dim pkg
>> Set pkg = DTSGlobalVariables.Parent
>> pkg.LoadFromSqlServer (local),"sa",,,,,,"pkg"
>> pkg.Execute
>> End Function

>> pkg is the DTS package executed.

>> It does not stop running. Goes into some kind of an infinite execution. I
>> then have to start the NT task manager and delete the task. I tried
>> pkg.Uninitialize(), but it didn't help.


>> >Use DTSGlobalVariables.Parent to get the package handle, e.g.

>> >Dim pkg

>> >Set pkg = DTSGlobalVariables.Parent
>> >pkg.LoadFromSQLServer ...
>> >pkg.Execute

>> >    Tom

>> >---


>> >How do i run a DTS package from another package's ActiveX script? Is
>there
>> a
>> >'Run' command. I though of using the Execute method, but at this stage
am
>> >not able to figure out how to get the Package handle to do this. (The
>> >examples given in Books on-line show how to get the handle of the
current
>> >package.)


>> >>You can either use ActiveX Script to run the package or use the
>> >>ExecuteProcess
>> >>Task to call DTSRun and execute the other package.

>> >>-Euan


>> >>> Hi,

>> >>> I have a DTS package which runs the main loop. It has to call another
>> DTS
>> >>> package from within the loop. How can i achieve this?

 
 
 

Run a DTS package from another DTS package

Post by Darren Gree » Fri, 13 Aug 1999 04:00:00




<snip>
Quote:>Also is there a way to dynamically change the Source File of ImpData
>package. Currently i am copying each file to TemDataFile.txt and executing
>the DTS Package.

<snip>

Yes, if you look at the "Text File Connection file name" article at
http://www.swynk.com/friends/green/ some examples are given.

In your case, after you have loaded the ImpData package you could set a

     Set cn = oPKG.Connections("Text File Source step Name")    
     cn.DataSource = "r:\eisdesign\datafiles\filename.txt"  

This explained more fully on the pages mentioned above. Just remember
that those examples are designed to be run from within the same package
as the connection (Text File Source).

--
Darren Green