Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function

Get the InputGlobalVariables from a different package

I've been tasked with figuring out how to alter the version_id of an ExecutePackageTask programatically from a separate DTS package.

The ultimate goal is to have a "utility" DTS pack that
iterates through all the child packages within a given parent package, checks the systems tables to get the latest version_id, then assigns the latest versionid to the ExecutePackageTask. We feel this beats the heck out of opening the parent, opening each ExecutePackageTask, running through the randomly ordered list of Package Names and clicking on the updated package when a child package changes or a program is migrated to a new server.

My approach was to open the parent pack from an activex script in the utility DTS package, then iterate through the task collection looking only at ExecutePackage tasks, then check the versionid against the appropriate row in the sysdtspackages table and update the child package version_id if necessary.

I've only gotten as far as looking at the ExecutePackageTask properties and I'm stumped . . . I'm told that the object doesn't support the property or method, yet you can do this manually through a disconnected edit! Any thoughts?

Here's the code so far:
Function Main()

'create the target parent package object
Set objPackage = CreateObject("DTS.Package2")

'load the target parent package
objPackage.LoadFromSQLServer "PAMB","dts" ,"dts", DTSSQLStgFlag_UseTrustedConnection, , , ,"TESTME2"

'Create the task object
Set objTask = objPackage.Tasks

'Iterate through the tasks
For each objTask in objPackage.Tasks
if left(objTask.Name,29) = "DTSTask_DTSExecutePackageTask" then
msgbox objTask.Description
msgbox objTask.Versionid
end if
Next

Main = DTSTaskExecResult_Success

End Function

Thanks in advance,

PamClearly there are too many projects in my head right now . . .

Get server name

Is there any way to get the SQL Server's name without running SELECT
@.@.SERVERNAME? I want a DTS package that I am working on to be able to
migrate from the dev server to the live server without having any connection
s
to the dev server. To run the query to find the server name, you need a SQL
server to run the query on. However, the dev server is not guaranteed to be
accessible to run the query on and I don't know the name of the servers that
this will be migrated to.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi
Unless you are using a default connection your installation process should
really ask for the Sqlserver name/instance. You could look at deciphering th
e
output of NET START. If you only want the computer name the command prompt
command HOSTNAME will give you it.
You may want to look at:
http://www.sqldts.com/default.aspx?242
John
"Chris Lieb" wrote:

> Is there any way to get the SQL Server's name without running SELECT
> @.@.SERVERNAME? I want a DTS package that I am working on to be able to
> migrate from the dev server to the live server without having any connecti
ons
> to the dev server. To run the query to find the server name, you need a S
QL
> server to run the query on. However, the dev server is not guaranteed to
be
> accessible to run the query on and I don't know the name of the servers th
at
> this will be migrated to.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps