Hello!

How do you dynmaically change sql server connections (data source)
through ActiveX script?

This code is not working for me:

DTSGlobalVariables("gv1").Value = server1
DTSGlobalVariables("gv2").Value = server2

//I am trying to pass server name..

'gv1' and 'gv2' are global variables defined in the 'dynamic properties'
task. The sequence of the DTS pacakge is:

1. Dynamic properties task
2. ActiveX
3. sql server connection 1 //data pump
4. sql server connection 2

Thanks for your help!



*** Sent via Developersdex http://www.developersdex.com ***

Hi,
As far as I know Is not possible do such thing. You only can read tasks and
connections collections and so on using dtspkg.dll with VB, for example.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)


"Test Test" wrote:

Hello Test,

I am not really sure what you want but if you want to change the server to
which a connection points then you can try something like this

DTSGlobalVariables.Parent.Connections("Name Of Server Connection").DataSource
= DTSGlobalVariables("Server Name Variable").Value



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I am trying to do this but the Data Pump Tasks that use this connection
don't seem to be using the new setting in the connection, although at
the end of the package I can see that the connection has been set. The
pumps just use the original connection.

Here is the progress:
1. SQL Task1: Gets server information from a SQL Server table, and sets
it to a recordset global variable in the DTS.
2. ActiveX Task1: Begins loop, and sets the connection to server2 and
sets sql of datapump tasks
3. Several data pumps run.
4. ActiveX Task2: just loops around
5. ActiveX Task1: Sets the connection object to server2 (msgbox tells me
this happens successfully)
6. The datapumps continue to use server2(??)
7. Package finishes succesfully
8. I right click on first datapump and do a "execute task". It executes
using server2, which for some reason it did not do during execution of
the entire package.

The loops works fine (as I have gotten it from you Allan), it clearly
goes through twice when I have specified two servers. If I run the data
pump tasks by right clicking and doing an "execute task" then it works
using the connection that was set.

I put a msgbox in the ActiveX Task1 and it is getting the server info:

Here is the part of the code of the activeX task that sets the
connection (let me know if you want to see the package):

Dim pkg
Dim SCCScon
Dim objRS
Dim SQLStatement

set pkg = DTSGlobalVariables.Parent
set SCCScon = pkg.Connections("Connection_Name")

set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")

Set objRS = DTSGlobalVariables("gvDataSources").Value


stpFinished.DisableStep = true
stpEnterLoop.DisableStep = false
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

msgbox "DSN is now: " & SCCScon.DataSource

SCCScon.DataSource = objRS.Fields(1)
SCCScon.UserID = objRS.Fields(2)
SCCScon.Password = objRS.Fields(3)

msgbox "Now I've set the DSN and it is now: " & SCCScon.DataSource

Thanks,
Kayda



*** Sent via Developersdex http://www.developersdex.com ***