DTS - loop through ADO recordset- HELP!!!

Question

DTS - loop through ADO recordset- HELP!!!

roxindigo

SSC Journeyman

Points: 97
More actions
September 1, 2003 at 10:01 am

#83154

I am in dire need of assistance and have spent days going through articles and forum posts.
I have a DTS package that exports data to one or more text files based on a table query. The package starts with an SQL Execute task that returns a rowset as a global variable. The next task, that executes on successful completion of the SQL execute task, is an ActiveX script task that extracts global variable values that are used as input parameters to the query that the Data Transformation task uses next. In addition, each text file that is exported has a unique file name that is created at runtime using the global variable information. The first ActiveX script generates/assigns the first file name and the input parameters for the data transformation task. The first text file exported is perfect. The task that bombs on me is the second ActiveX script that follows the Data Transformation step. This second ActiveX script is supposed to generate the remaining text files by placing the Data transformation task in a waiting state. All of the remaining text files are named and generated, but are empty. I know that there is valid data to export for these files because I tested the data transform for each set of inputs. Following is a copy of my 2nd ActiveX script. Please help me to understand why this isn't working. I'm new to both vbscript and DTS, so forgive my obvious ignorance:
' Visual Basic ActiveX Script
'*************************************************
***********************
Option Explicit
Dim ado_mdsql_Connect
Dim strConnect_mdsql
Dim objresults
Function Main()
Dim oPKG
Dim osrcConnection
Dim odestConnection
Dim fsObject, sourcefile, destinationfile, fsTextFile
Dim cntr
set ado_mdsql_Connect = CreateObject("ADODB.Connection")
strConnect_mdsql = "Provider=SQLOLEDB;" & "Driver={SQL Server};" & "Server=SERVER-MD1;" & "Database=MDSQL;" & "Trusted_Connection=yes"
ado_mdsql_Connect.Open strConnect_mdsql
set objresults = CreateObject("ADODB.recordset")
Set objresults = DTSGlobalVariables("glbv_cust_invoice_nbrs").Value
Do until objresults.EOF
Set oPKG = DTSGlobalVariables.Parent
Set osrcConnection = oPKG.Connections("MDSQL Source")
objresults.MoveNext
If objresults.EOF then
DTSGlobalVariables("glbv_customer_nbr").Value = ""
DTSGlobalVariables("glbv_invoice_date").Value = ""
DTSGlobalVariables("glbv_invoice_nbr").Value =""
Main = DTSStepExecStat_Completed
else
DTSGlobalVariables("glbv_customer_nbr").Value = objresults("Cust_No").Value
DTSGlobalVariables("glbv_invoice_nbr").Value = objresults("Invoice_#").Value
DTSGlobalVariables("glbv_invoice_date").Value = objresults("Inv_Date").Value
Set fsObject = CreateObject("Scripting.FileSystemObject")
destinationfile = "\\server-md1\Raid5\Inetpub\ftproot\StoreEDI\"&objresults("Cust_No").Value&"i"&Right(objresults("Invoice_#").Value,3)&".txt"
Set fsTextFile = fsObject.CreateTextFile(destinationfile,True)
fsTextFile.Close
Set fsObject = nothing
Set odestConnection = oPKG.Connections("Destination")
odestConnection.DataSource = destinationfile
DTSGlobalVariables("glbv_txtfile_path_and_name").Value = destinationfile
oPKG.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus= DTSStepExecStat_Waiting
end if
loop
Main = DTSTaskExecResult_Success
End Function
Also,
Many thanks to Darren Green for his articles on SQLDTS. They were very helpful.
karen M Mason

Karen M Mason

Viewing 5 posts - 1 through 4 (of 4 total)

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

Maybe I'm a bit confused, but does the second ActiveX script run before the Datapump task? If so, then you probably just need a third ActiveX script after the Datapump task that sets the second script task to waiting.

Take a look at the diagram at

http://www.sqldts.com/default.aspx?246

particularly step 4, 6 and 7 of the package.

If I've misunderstood you, maybe you could list your package steps in order of execution.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

roxindigo SSC Journeyman Points: 97 More actions · Answer 2

quote:
Maybe I'm a bit confused, but does the second ActiveX script run before the Datapump task? If so, then you probably just need a third ActiveX script after the Datapump task that sets the second script task to waiting.
The first ActiveX script executes before the data transform task and initiates processing of the first text file export. No, the second ActiveX script executes after the data transform task for the first text file.
I thought that putting the data transform task in waiting mode in the ActiveX script that followed it would cause it to loop back and process the remaining exports. Are you saying that I need to add another script, or is there something that I can add to the second ActiveX script (the one that follows the data transform task) to make this looping mechanism work?
Thank you so much for responding. I work with a couple of guys who think you're dim if you can't accomplish any task in three days. I'm well past the three day "dim" limit.
Take a look at the diagram at
http://www.sqldts.com/default.aspx?246
particularly step 4, 6 and 7 of the package.
If I've misunderstood you, maybe you could list your package steps in order of execution.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

karen M Mason

Karen M Mason

philcart SSC-Forever Points: 47795 More actions · Answer 3

Ok, I did misunderstand

Based on what you have now, I'd add another global variable (maybe glbv_Counter) to act as a counter for your position in the rowset.

Then have your first script set the connection properties based on whatever number is in glbv_Counter. First time through it'll be record zero. Once you've set the connection properties, increment the counter variable.

Then your datapump task executes, which in turn executes the second script. In this script all you need to do is check if your counter variable is less than or equal to the number of records in the rowset. If it is set the execution of the first script to waiting.

This is all from memory at the moment. If you have any problems let me know and I'll try and put together some actual scripting code.

And don't worry about being dim, there are plenty of us out there to keep you company