Re: Script Controlled Dynamic Task Execution

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

Date: Thu, 28 Oct 2004 17:36:01 -0700

Thanks again for your help Allan. I like that elegant solution to that
problem, but it's different than the problem that I have. If I were to use
that process, I would have to change the format of the connection and the
transformations in the data pump. My package had three connections to three
different type of text file, and each had a transformation for itself.

After posting, I just created three packages for each connection, and my
task loops through their execution. Probably is a little less efficient, but
this is a once a day 40-minute process.

Thanks again!

"Allan Mitchell" wrote:

> OK
>
> Have a look at this, see if it makes sense to you, see if it does what you
> want then let us know
>
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
>
> "Brad" <Brad@discussions.microsoft.com> wrote in message
> news:8B743BE6-F50D-49C4-B80B-2D886FD60FE0@microsoft.com...
> > 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: Check connection to db
    ... The usual way would be to execute a dummy query against the server. ... If the connection is lost ... during package execution then the associated task will fail and you can set ...
    (microsoft.public.sqlserver.dts)
  • 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)
  • 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)
  • Re: New Schedule DTSX Job Fails to execute
    ... I use a sysadmin proxy account to execute the single steps. ... I then imported the package to my ... select Connection Manager and select the connections in the package ... and manually add the passwords to the connection strings. ...
    (microsoft.public.sqlserver.dts)
  • Re: Export to Text File Fails from Server Box
    ... If you are on WKSTN1 and you pick up a package from SVR1 and press execute, ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... >I should have specified in my question that in re-creating the text> connection, I did point in to a file local to the SQL Server. ... When I click on the>>> Destination tab, there is an execute button. ...
    (microsoft.public.sqlserver.dts)