I have a DTS Package that has 2 tasks and 1 connection.
Tasks
1. ActiveX module that reads connection properties from a text file and
iterates thru them changing the connection properties of the Package
connection.
2. ExecuteSQL task that has my script and uses the connection.

Here is my problem.
ActiveX iterates thru the text file. After it changes the connection
property I want it to run the Execute SQL task.

While Not (nIn.AtEndOfStream)
strLine = nIn.ReadLine
msgbox left(strLine,instr(strLine,",")-1) 'test msgbox for server name
msgbox right(strLine,len(strLine)-instr(strLine,",")) 'test msgbox for
database name

strLineServer = left(strLine,instr(strLine,",")-1) 'assign the server
name
strLineDB = right(strLine,len(strLine)-instr(strLine,",")) 'assign the
database name

AddGlobalVariables = True 'do not delete this statement

Set oPackage = DTSGlobalVariables.Parent
Set oConnection = oPackage.Connections(1) '1 being the database
connection

oConnection.Datasource = strLineServer
oConnection.Catalog = strLineDB
oConnection.Userid = "xxxxxxxx"
oConnection.password = "xxxxxxxx"
oPackage.tasks(2).execute 'Here is the problem. I want it to run this for
each connection change.
Wend


Any Suggestions?

Thanks

Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451

In message <e5Sm$auJEHA.2452 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Ron Sissons
<RSISSONS (AT) rcoe (DOT) k12.ca.us> writes

Ron,

The classical DTS way of doing this would be to reset the first step's
ExecutionStatus to DTSStepExecStat_Waiting, causing a loop. Some
examples here-

Workflow
(http://www.sqldts.com/default.aspx?103)

To be honest I think this overkill, you hardly need DTS. If you have the
full connection details then I would just use ADO within the while loop
all within the same task.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

"Ron Sissons" <RSISSONS (AT) rcoe (DOT) k12.ca.us> wrote


Hi,
I think You should use oPackage.Steps instead of Tasks.
In your pkg you'll probably have 2 Steps, each one associated with a
task, and a connection.
While you execute Step1, which iterates for changing connection props,
you should avoid execution of Step2 as you submit it by your own
script so, from Disconnected Edit wizard you should disable Step2 from
Package execution.
If you like i can send you a simple example.
Bye

Davide Rossetti
rosdav007 (AT) hotmail (DOT) com
Italy