• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • 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.
1 Solution
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"

Set pkg = Nothing
Set tsk = Nothing
Set Con = Nothing
End Function
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 
Post your closing recommendations!  No comment means you don't care.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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