Script Controlled Dynamic Task Execution

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Brad (Brad_at_discussions.microsoft.com)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 05:17:01 -0700

Good morning.

I have a package in which I have three text files with three matching
Transformation Tasks into my SQL Server database. My problem is that I may
need to execute one transformation on a text file, then execute that same
transformation on another text file, and skip the other two text files and
their transformations.

So essentially, I have a script that wants to take complete control of the
Package as far as source connections and task executions, however I'm not
completely sure how to do this.

My script changes the source connections for each Text Connection item, but
then it is unable to execute the task and furthermore stop Package execution
once the Script has finished its run.

Here's the meat of this part of the script:
[code]
' Reports is now built. Iterate through it evaulating the Report Name
        
' Iterator
dim v
        
' This package
dim oPkg
set oPkg = DTSGlobalVariables.Parent

' The DataPumpTask to execute
dim oTsk
For Each v in Reports
        ' Evaluate the name using a select statement.
        Select Case v.ReportName
                Case "Historical\Designer\Split Summary"
                        oPkg.Connections("Split Statistics").DataSource = v.ExportFileName
                        set oTsk = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
                Case "Historical\Designer\Interval Productivity Detail by Split"
                        oPkg.Connections("Agent Statistics").DataSource = v.ExportFileName
                        set oTsk = oPkg.Tasks("DTSTask_DTSDataPumpTask_2").CustomTask
                Case "Historical\Designer\Interval Login/Logout by Split"
                        oPkg.Connections("Agent Logout Statistics").DataSource = v.ExportFileName
                        set oTsk = oPkg.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask
                Case Else
                        ' Unhandled. Do something about this.
        End Select
        ' The source file has been changed. Now, execute the transformation task.
        oTsk.Execute
Next
' Now exit the package since we're done.

[/code]
I know that oTsk.Execute does not work because of missing parameters, and
looking at the parameters I'm supposed to specify I get even more confused.
The Execute method of the CustomTask object has these parameters:

pPackage, pPackageEvents, pPackageLog, pTaskResult

I'd really appreciate any feedback on what I'm trying to do here.

Thank you!

Relevant Pages

  • Re: Script Controlled Dynamic Task Execution
    ... My package had three connections to three ... and each had a transformation for itself. ... After posting, I just created three packages for each connection, and my ... >> need to execute one transformation on a text file, ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS & Script Transformation (debug vs normal)
    ... Can you give more details about what the "transformation script" is ... The package is pretty simple. ... but not when ran by sql agent. ...
    (microsoft.public.sqlserver.dts)
  • Re: Using Python in ActiveX task - works when run as separate step
    ... I just used some simple script to test: ... I installed ActivePerl on my test machine, create a new DTS package, and a ... new ActiveX script task. ... |>>>You could execute the step in DTS package window. ...
    (microsoft.public.sqlserver.dts)
  • Re: ActiveX script works in DTS but not in job, why?
    ... When you execute the package, are you logged in as the same user under which ... and run the DTS package? ... Peter DeBetta, MVP - SQL Server ... The script works just great ...
    (microsoft.public.sqlserver.dts)
  • Re: Script Controlled Dynamic Task Execution
    ... > Transformation Tasks into my SQL Server database. ... > need to execute one transformation on a text file, ... I have a script that wants to take complete control of the ... > Package as far as source connections and task executions, ...
    (microsoft.public.sqlserver.dts)