Hi,

Here is my problem:

I have an Excel file containing a list of funds. I can open it easily enough
and read the funds.

Now I need to execute a Transformation task for each fund by looping through
the funds in the Excel file, and execute a SQL Query using the currect fund
against a table, then output that to a CSV file.

So inside the llop I need to change the SQL of the Data Transformation task,
execute it, then continue to the next one...

I have a Data Pump Transformation task, and I can retrieve the SQL
Statement, and reset it...

so how can I execute the Data Pump once for each fund?

here is my ActiveX Script so far...
======================
Function Main()
dim oFso, sDate, sMonth, sDay, oFile, sYear, sTime
dim sSrcLocation, sWorkingDir, sWorkingFile, sArchiveDir, sArchiveName

dim objExcel, xlWorkbook, xlSheet
dim sSrcPath, sSrcFile, sFund, oDataPumpTask, oDataPump

Set oPkg = DTSGlobalVariables.Parent

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
' oDataPump.SourceSQLStatement = sSQLStatement

msgbox oDataPump.SourceSQLStatement

' Get Global Vars
sSrcPath = DTSGlobalVariables("SrcPath").value

set objExcel=CreateObject("Excel.Application")

set xlWorkbook = objExcel.workbooks.open(sSrcPath)
set xlSheet = xlworkbook.worksheets("control")
xlsheet.activate
msgbox(xlsheet.range("A2").text)
xlsheet.range("A2").activate

Do While Not objExcel.activecell.Text = ""
sFund = trim(objExcel.activecell.Text)

objExcel.activecell.offset(1, 0).Select
Loop

xlworkbook.close true
objExcel.quit
End Function
======================

thanks for any tips or help, or code that will help me...

Philip

Hi,

this is where I am now...

My problems are
1)that although it sets the destination file of the DTS Data Pump task, it
doesn't create the files
2) it loops forever...

==========
Function Main()

dim oFso, sDate, sMonth, sDay, oFile, sYear, sTime
dim sSrcLocation, sWorkingDir, sWorkingFile, sArchiveDir, sArchiveName

dim objExcel, xlWorkbook, xlSheet, sSQLStatement
dim sSrcPath, sSrcFile, sFund, oDataPumpTask, oDataPump

Dim conTextFile
Dim stpEnterLoop
Dim stpFinished

Set oPkg = DTSGlobalVariables.Parent

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump

' Get Global Vars
sSrcPath = DTSGlobalVariables("SrcPath").value

set objExcel=CreateObject("Excel.Application")

set xlWorkbook = objExcel.workbooks.open(sSrcPath)
set xlSheet = xlworkbook.worksheets("control")
xlsheet.activate

xlsheet.range("A2").activate

Do While Not objExcel.activecell.Text = ""

sFund = trim(objExcel.activecell.Text)
'msgbox sFund

set stpEnterLoop = oPkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = oPkg.Steps("DTSStep_DTSActiveScriptTask_2")
set conTextFile = oPkg.Connections("DestinationFile")

sSQLStatement = "SELECT Fundcode, AccountFrom, AccountTo FROM
AD_FundAccountLinks where fundcode = '" & sFund & " ' "
stpEnterLoop.DisableStep = False
oDataPump.SourceSQLStatement = sSQLStatement
'msgbox oDataPump.SourceSQLStatement

conTextFile.DataSource = sFund & ".csv"
msgbox conTextFile.DataSource

stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

objExcel.activecell.offset(1, 0).Select
Loop

stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting

xlworkbook.close true
objExcel.quit
set objExcel=nothing
set oPag=nothing

Main = DTSTaskExecResult_Success

End Function
==========

I'd be grateful for any help or assistance with this...

thanks
Philip