I am trying to figure out thehow to get to the connection properties for a Execute SQL task.
I want to change the sql task connection on the fly. Here is what I have

For Each oTask in oPKG.Task

If oTask.CustomTaskID = "DTSExecuteSQLTask" the
----Here is where I am confused. Where do I find reference to the existing connection property in the task
----What is the property to access and change the existing connection of an xecute SQL task? Is there a list of properties -------that an execute SQL task has
msgbox oTask.Properties("Description").valu
----End confusio
' Examine Connection
For Each oConnection in oPKG.Connection
If oConnection.ProviderID = "SQLOLEDB" and oConnection.name = "xxxxxx" The
msgbox oConnection.name & oConnection.datasource & oConnection.descriptio
End I
Nex
End I
Next

Better format....

I am trying to figure out the how to get to the connection properties for a
Execute SQL task.
I want to change the sql task connection on the fly. Here is what I have.

For Each oTask in oPKG.Tasks

If oTask.CustomTaskID = "DTSExecuteSQLTask" then
----Here is where I am confused. Where do I find reference to the existing
connection property in the task.
----What is the property to access and change the existing connection of an
xecute SQL task? Is there a list of properties -------that an execute SQL
task has?
msgbox oTask.Properties("Description").value
----End confusion
' Examine Connections
For Each oConnection in oPKG.Connections
If oConnection.ProviderID = "SQLOLEDB" and oConnection.name = "xxxxxx"
Then
msgbox oConnection.name & oConnection.datasource &
oConnection.description
End IF
Next
End IF
Next


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

OK

You want to change the properties of the connection to which your ExecuteSQL
task points.

Does this work for you


Function Main()

dim pkg, con, tsk


'ref to package
set pkg = DTSGlobalVariables.Parent

'Ref to task
set tsk = pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTas k

'The ConnectionID property of the task is the ID of the Connection

set con = pkg.Connections(tsk.ConnectionID)

'con.DataSource = Server
'con.Catalog = Database


Main = DTSTaskExecResult_Success
End Function


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Rsissons" <rsissons (AT) rcoe (DOT) k12.ca.us> wrote

task has?

OK,

I can change the connection that the ExecuteSQL task points to.
Now I want to change it 23 times with different datasource and catalog.
Would the best way be to put all the connection values in a text file and
read them in to change the one connection properties?


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



You want to change the properties of the connection to which your
ExecuteSQL
task points.

Does this work for you


Function Main()

dim pkg, con, tsk


'ref to package
set pkg = DTSGlobalVariables.Parent

'Ref to task
set tsk = pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTas k

'The ConnectionID property of the task is the ID of the Connection

set con = pkg.Connections(tsk.ConnectionID)

'con.DataSource = Server
'con.Catalog = Database


Main = DTSTaskExecResult_Success
End Function


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Rsissons" <rsissons (AT) rcoe (DOT) k12.ca.us> wrote

Execute SQL task.
connection property in the task.
SQL
task has?

You certainly can

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

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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