Hi:

I have had experience doing the following with ActiveX script tasks:

1. Setting the SQL Statement of a data pump task so I can change the
WHERE clause before that task executes.
2. Dynamically setting the tablename for tables like MMDDYYYTableName,
so the data pump task knows the right table name.

Now I need to do this: I'm working with text files and need to set the
text file name from an ActiveX task(which I assume will be similar to
setting the table name--set the SourceObjectName property of the data
pump). The thing is, the task might need to run multiple times
depending on how many files I need to read. So here is what should
happen:

1. First of all, some info: Data for each one hour interval is stored
in a file DDMMYYHHMMTextName.txt. Where HHMM is the beginng of a one
hour interval (i.e. 9-10 a.m. is 0900. The DTS package will normally
read one of these files each hour just after it is created. But for
some reason it might miss reading a file.

2. When package starts, read the newest interval value in my SQL Server
table where I am storing previously pumped data. If the time is
currently 2:05 p.m., but the newest interval is 09:00, then there must
have been a problem before and I want to try to read each interval
between then and now. So I establish the files I need and write them to
a table for the ActiveX task to use.

050820051000TextName.txt
050820051100TextName.txt
050820051200TextName.txt
050820051300TextName.txt

3. Run the datapump multiple times, passing in a different value for
each text file each time.

Obviously there will usually only be one text file to read if
everything is running smoothly, but there might be more as above. I
want to only create one datapump task, but is there a way to run it
from an ActiveX script task, so it can run the amount of times I need
it to rum, setting the SourceObjectName property (if I'm right and that
is what I want to set) each time? Or is there a completely different
way to accomplish what I want to accomplish?

This will help a lot-I've got my data connections to the text files all
set up (and have figure out how to parse these files, which are kind of
strange for text files!), if I can figure out the above I will be home
free.

Thanks a lot,
Kayda

Would this sort you out?

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.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"kayda" <tabladude (AT) gmail (DOT) com> wrote

Hi Alan:

I found that article just before you replied, but I'm having problems
tailoring it for my needs. What I'm dong is different (not checking
directories yet, not using GVs, I've got an extra SQL task in my loop).
Here is what I have (2 ActiveX tasks, 2 datapump tasks, 1 source
connection(text), 1 Destination(local SQL Server), 1 Execute SQL Task):

DTSStep_DTSActiveScriptTask_1 - "Begin Loop" (similar to yours, code
below)
Text Connection : "AgentContactData" ('Begin Loop' ActiveX sets
filename and path of this)
DTSStep_DTSDataPumpTask_1 - Dependent on success of
DTSStep_DTSActiveScriptTask_1
SQL Server Connection
DTSStep_DTSExecuteSQLTask_2: (Cleans up messy text data) Dependent on
success of DTSStep_DTSDataPumpTask_1
DTSStep_DTSActiveScriptTask_2 : "Loop Around" (similar to your "Loop
Around"). Dependent on success of DTSStep_DTSExecuteSQLTask_2
DTSStep_DTSDataPumpTask_2: What should be executed when loop ends.
Dependent on success of DTSStep_DTSActiveScriptTask_1

What happens is the Begin Loop ActiveX runs, setting the textfilename
for the pump and supposedly setting the looping properties. The data
pump runs, and then the SQL Task runs to clean up some dirty text data
(and sets a flag in the database saying I'm done with that last text
file I just read). The "Loop Around" ActiveX doesn't run for some
reason, and none of the other tasks run. It should have looped around a
second time, as there are two text files I am testing on for now. Here
is my code for the ActiveX tasks only (I don't think the code of the
SQL task is relevant, but let me know if you need this):

DTSStep_DTSActiveScriptTask_1 (Begin Loop)
Option Explicit

Function Main()
Dim pkg
Dim conTextFile
Dim stpEnterLoop
Dim stpFinished
Dim objconn
Dim strSQL
Dim FilePath
Dim objRS

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSDataPumpTask_2")
set conTextFile = pkg.Connections("AgentContactData")

'Only if there are values left to write do we want to continue

Set objconn = CreateObject("ADODB.Connection")
objconn.open = ("provider = sqloledb; data source = (local);user id =
sa;password = password")
objconn.DefaultDatabase = "CoolDB"

strSQL = "SELECT Date_time, Filepath FROM Interval_Update_List " &_
"WHERE Date_time = (SELECT MIN(Date_time) FROM Interval_Update_List "
&_
"WHERE AgentContact_Done=0)"

Set objRS = objconn.execute(strSQL)

IF NOT objRS.EOF then
FilePath = objRS.Fields("Filepath")
ELSE
FilePath = "No_More_Intervals"
END IF

objRS.Close
objconn.Close
Set objRS = nothing
Set objconn = nothing

if FilePath <> "No_More_Intervals" then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
conTextFile.DataSource = FilePath
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success
End Function

Main()
__________________________________________________ _____
DTSStep_DTSActiveScriptTask_2 (Loop Around)
Option Explicit

Function Main()

Dim pkg
Dim stpbegin

set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function

Main()
________________________________________________
______________________________________________

Thanks for any help Allan,
Kayda

I wouldn't be readin my rowset in here. I would bee doing it like this

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

If your rowset holds 2 rows then that is the amount of times you will loop

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"kayda" <tabladude (AT) gmail (DOT) com> wrote

Hi Allan:

I'm trying to figure this out, and how to apply it to what I've got.
The Interval_Update_List table is built at the beginning of the
package, containing a field for each 30 minute interval I need, and
fields for each of the full paths of various files I'll need for that
interval (for each interval there are different files I'll need, but
for now I'm just concerned with this one file).

So it sounds like you are saying I should built a record set with a
select in an SQL task, which is written to a global variable, which I
read in my Active X task. A few questions:

1. The global variable showed in the package shows up as type
"Dispatch". I cannot see that type to select.
2. How does the SQL task know what variable to write to (I don't see
reference to it in the SQL Task)?
3. In your sample there is "Task" acitveX code and "Workflow" code.
I'm not sure where the workflow code goes.

Thanks,
Kayda

1. Add the GV as a string and DTS will do the rest
2. You hit the Parameteres button and then on the last tab there (Output
Parameters) you will find what you need
3. Right click on the task | Workflow | Workflow Properties | Options Tab |
Use Active X Script checkbox | Hit properties button


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"kayda" <tabladude (AT) gmail (DOT) com> wrote

Hi Allan:

So here is my approach right now (the sqldts.com site doesn't appear to
be working, just get a blank page):

SQL Task (builds intervals table with interval and filepath, sets
recordset output parameter by doing a SELECT * on the table it just
built) --> Begin Loop ActiveX task (sets the text file path and sets a
date gv) -->Text Data Pump --> SQL task (to clean up data and set
datetime vaue based on gv set earlier) --> Loop Around ActiveX Task
(workflow checks if EOF, if so, move on. If not, loop) --> Next data
pump (waits for success of Loop Around Task).

I'm getting an error code 0 object required (objRS) error in the first
ActiveX task. I checked I've been consistent with the object name, not
sure what the problem is. My ActiveX Code is at the bottom.

My questions:
1. Could the problem having something to do with setting my output
parameter in the first SQL task? The behaviour in the task properties
is a little different than yours--to create the output parameter I had
to fake an input parameter (put a question mark in a WHERE clause) to
allow me to press the 'parameters' button (because it was telling me
that my query didn't contain parameters). Then I can set the output
parameter, and remove the bogus input parameter. If I go back again to
make sure the output parameter is there, I have to fake an input
parameter again. With the sample DTS from sqldts.com, I can just click
on parameters and go right in.

2. Is my general approach look good? I've simplified what was in the
example-I didn't see a need for workflow script on the first task in my
case, it would always have a record to read, and the final ActiveX task
moves us on out of the loop. I'm now using a gv for my date value,
which I'm intending to use in the SQL task that follow the data pump
(although I'm having problems using that input parameter in the Execute
SQL task, I know there is an article on this on sqldts.com, which I'll
have a look at when I am able to access the site again).

_______________________________________
Begin Loop Workflow Task
------------------------------------------------------------------
No workflow task active
________________________________________
Begin Loop Task Code
-----------------------------------------------------------------------
Function Main()
Dim pkg
Dim conTextFile
Dim FilePath
Dim objRS

set pkg = DTSGlobalVariables.Parent
set conTextFile = pkg.Connections("AgentContactData")
Set objRS = DTSGlobalVariables("Intervals_RecordSet").Value
FilePath = objRS.Fields("Filepath")
DTSGlobalVariables("Date_Time_For_Pump").Value =
objRS.Fields("Date_time")

conTextFile.DataSource = FilePath

IF NOT objRS.EOF then
objRS.MoveNext
END IF

Set objRS = nothing

End Function

____________________________________________
Loop Around Workflow Code
____________________________________________
Option Explicit

Function Main()

Dim oRS
Dim BeginLoopTask
Set oRS = DTSGlobalVariables("Intervals_RecordSet").Value

' Check if there are still more records in the Recordset
If oRS.EOF Then
' No more records, so continue workflow
Main = DTSStepScriptResult_ExecuteTask
Else
' More records to process, so loop back and do not execute this task
set pkg = DTSGlobalVariables.Parent
set BeginLoopTask = pkg.Steps("DTSStep_DTSActiveScriptTask_1")
BeginLoopTask.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSStepScriptResult_DontExecuteTask
End If

Set oRS = Nothing

End Function

_______________________________________________
Loop Around Task Code
_______________________________________________
Option Explicit

Function Main()

Main = DTSTaskExecResult_Success

End Function

Thanks as always,
Kayda