Solved

DTS Re-using Connection object within Activex script

Posted on 2002-06-10
3
673 Views
Last Modified: 2012-08-14
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
Comment
Question by:lozzamoore
3 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 100 total points
ID: 7068427
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
 

Expert Comment

by:CleanupPing
ID: 9280367
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question