How do I execute a step within a Record Set loop

Question

How do I execute a step within a Record Set loop

Mark Bosomworth

Say Hey Kid

Points: 669
More actions
April 26, 2005 at 3:33 am

#66048

I am loading a global record set with a list of tables. As I loop through the record set I would like to call another step to copy the data out to a file. All appears to work fine however, only the last table is being copied out.
How can I call another DTS step from within my activex script within the record set loop? Code below, failed attempts commented out.
Function Main()
dim countr, pkg, stpExport, stpRS
dim RS
Set RS = CreateObject("ADODB.Recordset")
Set RS = DTSGlobalVariables("ExportTable").value
Set pkg = DTSGlobalVariables.parent
Set stpExport = pkg.Steps("DTSStep_DTSDynamicPropertiesTask_1")
Set stpRS = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
for countr = 1 to RS.RecordCount
DTSGlobalVariables("CommandLine").Value = "BCP " _
      & DTSGlobalVariables("SourceObject").Value _
      & RS.Fields("tablename").value _
      & " out " _
      & DTSGlobalVariables("TargetObject").Value _
      & RS.Fields("tablename").value _
      & ".csv -T -c -t" & """" & DTSGlobalVariables("FieldTerminator").Value & """"
'MsgBox "Tables to export are " & RS.Fields("tablename").value
'Msgbox DTSGlobalVariables("CommandLine").Value
    'stpExport.DisableStep = False 'Step to be executed
    'stpRS.DisableStep =True
    'stpExport.ExecutionStatus = DTSStepExecStat_Waiting
    RS.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
Any help much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 1

The 'for' loop you are using seems to be the problem, because the RS.RecordCount property does not work as expected. In my experience it always gives -1 as the value. Try using:

While Not RS.EOF

----
----
RS.MoveNext

Wend

instead. It could work. Good Luck

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 2

Thanks for the reply.

The problem is not that it does not loop. The msgboxs display the changes OK.

I cannot get another DTS step to be executed for each iteration of the loop, it just executes for the final record of the set.

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 3

Mark,

You are not executing anything from inside the loop. By setting the execution status of stpExport to "waiting" you are merely saying that stpExport is waiting to be executed after the current step is finished. And, of course at that point, your command line global variable contains only the data from the last record of the record set.

To execute a step the syntax is:

stpExport.Execute

But, you really shouldn't do it this way.

If you are writing to a .csv file, a much easier way to do it is to use a File System Object; create the csv file; and then write to the csv file with every iteration of the loop, as follows:

Dim strCsvLine, objFileSystem, objCsvFile, RS, countr

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objCsvFile = objFileSystem.CreateTextFile("C:\MyFolder\MyFile.csv", True)

Set RS = CreateObject("ADODB.Recordset")

Set RS = DTSGlobalVariables("ExportTable").value

for countr = 1 to RS.RecordCount

strCsvLine = "..."

objCsvFile.WriteLine(strCsvLine)

RS.MoveNext

Set objFileSystem = Nothing

Set RS = Nothing

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 4

Thanks for the reply Edwin.

The reason I would like to execute the step in the loop is that the next step is a dynamic properties task. This uses data in the record set as the file name and also a table name aswell. This then runs an execute process task which does a bcp.

I basically want to read a table which contains several tablenames, then bcp the listed tables using the tablename as the filename, for each tablename in the record set.

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 5

OK.

But, you don't need to execute a dynamic properties task. You can instantiate the task which does your bcp'ing and then set the properties for the task using the instiated object. The property names will be the same names as you would find in the dynamic properties task. In fact, if you call the dynamic properties task, and that task moves on to another task, I don't know if control would ever return to the task which called the dynamic properties task. I don't know because I've never tried that.

But, be that as it may, you don't need to call the dynamic properties task. You can set task properties in code and then execute the task with the following code. The ExecuteSQL task used in this example is a disconnected task (no workflow in or out) and is disabled.

Dim objPackage, objTask_ExecuteSQLTask, objStep_ExecuteSQLTask

Set objPackage = DTSGlobalVariables.Parent

Set objTask_ExecuteSQLTask = objPackage.Tasks("DTSTask_DTSExecuteSQLTask_1")

Set objStep_ExecuteSQLTask = objPackage.Steps("DTSStep_DTSExecuteSQLTask_1")

'Set Execute SQL task properties

objTask_ExecuteSQLTask.CustomTask.SQLStatement = "..."

'Call the Execute SQL task

objStep_ExecuteSQLTask.DisableStep = False

objStep_ExecuteSQLTask.Execute

objStep_ExecuteSQLTask.DisableStep = True

If objStep_ExecuteSQLTask.ExecutionResult = 1 Then 'Step Failed

.

End If

Set objTask_ExecuteSQLTask = Nothing

Set objStep_ExecuteSQLTask = Nothing

Set objPackage = Nothing

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 6

Thanks a load Edwin. That does just what I need.

chuz mark