[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

DTS Re-using Connection object within Activex script

Hey guys!

I have a DTS package with an Activex task step that I want to use to read some values from  a table and act depending on the contents.

I already have a connection object to the server and db that I want to use. Can I create a reference to the connection task within the body of the Activex code that I can then use as a ole db connection object?

Hope this makes sense.
0
lozzamoore
Asked:
lozzamoore
1 Solution
 
spcmnspffCommented:
You can do something similar to this by craeting an execute sql task on the fly, setting the connection to an existing connection object, and executing the task.  This isn't ADO and OLEDB but if your connection conscience, this saves you from creating a second connection to the same server/database.  The only drawback is the absence of a recordset object to contain any results.  You could load any return data in global table variables, etc . . . .

Private Sub Main()

Dim pkg, con, tsk

'Set reference to package object
Set pkg = DTSGlobalVariables.Parent
'Set reference to a connection by name or by number
Set con = pkg.Connections("My SQL Server Connection")
'Create a new execute sql task object
Set tsk = CreateObject("DTS.ExecuteSQLTask2")

'Set the connection
tsk.ConnectionID = con.ID
'Set the sql
tsk.SQLStatement = "Select top 1 * from Sysobjects"
'execute
tsk.Execute

Set pkg = Nothing
Set tsk = Nothing
Set Con = Nothing
End Function
0
 
CleanupPingCommented:
lozzamoore:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now