Reference a connection in DTS activeX

by John Fulle » Sat, 13 Jul 2002 02:54:51

Can a connection in a DTS package be referenced and used
in an ActiveX script in the same DTS package?

1. Referencing Data Link connection in ActiveX script

Hello,
I'm importing data from an Excel file into a Database, and I'm using a
Data Link to state the connection properties of the database.
However, I do a lot of transformations on the data along the way, in
ActiveX script, and I need to run some queries on the database in this
ActiveX script.

I used to have "Server" and "Database" variables provided by the user,
and then use them as follows:

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open = "Provider=SQLOLEDB.1; Data Source="+Server+"; Initial
Catalog="+DB+"; Integrated Security=SSPI"

SQLText = "Select CustID from tCustomer Where CustName =
'"+DTSSource("Customer Name:")+"'"

rs.Open SQLText, conn

But now that I have a Data Link to hold those values, I now longer
need them passed in from the user - but I still need to know how to
execute this query on the database, inside the ActiveX script.
I know I can reference the Data Link connection like so:
Set pkg = DTSGlobalVariables.parent
Set udl = pkg.Connections("Microsoft Data Link")

But how do I use that to open a connection to the database, run a
query and return a recordset, during my transformation???

Any help would be appreciated - I haven't found any articles on this!

Cheers,
Bronwyn.

2. Getting a from to show random results

3. Can ActiveX Script task reference a Db connection

4. QA - QUALITY ANALYST LEAD,Minneapolis,MN

5. DTS - Changing Connection properties from ActiveX script

6. Access 2000

7. ActiveX script in SQL Server DTS package not updating DataSource in Oracle connection

8. Stored Procedure behavior [newbie] HELP!!!

9. How to DELETE DTS connection references

10. DTS Transformation ActiveX Script Problem - Your Brain On DTS