Using code examples from sqldts.com, I'm trying to use the same datapump task
to import data from multiple source databases, ito a single db.
All of this occurs on the same server.

The code snippet below will loop thru once for each entry in the companyinfo
table, and the connection object shows the correct database each time.
But the data always comes from the first database .
The msgbox shows the correct database, and the rpf increments correctly.

Any Ideas?





'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
DIM connLocal
DIM rs1
dim strSQL
dim oPKG
dim oConnection

'instantiate DTS objects
Set oPKG =DTSGlobalVariables.Parent
set stpEnterLoop = opkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = opkg.Steps("DTSStep_DTSActiveScriptTask_2")
stpEnterLoop.DisableStep=True

' instantiate the ADO objects
set connLocal = CreateObject("ADODB.Connection")
connLocal.ConnectionTimeout=45
connLocal.CommandTimeout=90
set rs1 = CreateObject("ADODB.Recordset")



'LoadSite
connLocal.Open = "Provider=SQLOLEDB.1;Data Source=MMSMainVS; Initial
Catalog=RMX10;Application Name='GetPICs';Integrated Security='SSPI'"
strSQL="Select top 1 companyid,companyname,DBName from companyinfo where
active= 1 and companyID<95 and companyID >"
strSQL=strSQL & DTSGlobalVariables("RPF").value
strSQL=strSQL & " order by companyid"
rs1.open strSQL,connLocal
IF not rs1.eof THEN
Set oConnection = oPKG.Connections("SiteData")
oConnection.Catalog = rs1.fields(2)

DTSGlobalVariables("RPF")=rs1.fields(0)
msgbox(oConnection.Catalog & " " & DTSGlobalVariables("RPF").value)
stpFinished.DisableStep=TRUE
stpEnterLoop.DisableStep=FALSE
stpEnterLoop.ExecutionStatus=DTSStepExecStat_Waiti ng

END IF
IF rs1.EOF THEN
stpFinished.DisableStep=FALSE
stpEnterLoop.DisableStep=TRUE
stpFinished.ExecutionStatus=DTSStepExecStat_Waitin g
END IF
rs1.close
conn.close
Main=DTSTaskExecResult_Success


End Function

Are you setting the connection close on completion in the workflow
properties ?

Also if you are using 2000 you can get rid of the insantiating the ADO
stuff.

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

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


<bb_43 (AT) hotmail (DOT) com> wrote

In article <ubzA$E01DHA.2528 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote:

The problem doesn't seem to lie with the ADO connection and it's recordset in
the vbscript.
It invokes the data pump task for each record, and if I cancel in the middle,
the source DB is set to the correct one.
What SEEMS to be the problem is this:
Although the source connection is set to the proper db and the data pump task
shows the correct data if I hit "preview", the data from the first db is
retrieved each of the 11 loops.

In article <ubzA$E01DHA.2528 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote:

Thanks!
I set the connection close workflow property and it works perfectly, now.