Referencing Data Link connection in ActiveX script

Referencing Data Link connection in ActiveX script

Post by Bro » Sat, 14 Dec 2002 00:49:43



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.

 
 
 

Referencing Data Link connection in ActiveX script

Post by Darren Gree » Wed, 18 Dec 2002 07:04:18




Quote:>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.

If you a want to use your UDL file in an ADO connection string try-

conn.Open "File Name=C:\MyUDL.udl"

If you are doing this in an ActiveX Transformation script then why not
use a lookup and save all the overhead of coding and creating the ADO
objects? I'm sure it will perform better through lookups as you get
parameter queries, and connection re-use as well as not having to create
a connection and a (big fat - expensive to create) recordset object for
each row processed.

How to Use Lookups in DTS
http://www.sqldts.com/default.aspx?6,107,277,7,1

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

Referencing Data Link connection in ActiveX script

Post by Bro » Wed, 18 Dec 2002 21:09:30





> >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.

> If you a want to use your UDL file in an ADO connection string try-

> conn.Open "File Name=C:\MyUDL.udl"

> If you are doing this in an ActiveX Transformation script then why not
> use a lookup and save all the overhead of coding and creating the ADO
> objects? I'm sure it will perform better through lookups as you get
> parameter queries, and connection re-use as well as not having to create
> a connection and a (big fat - expensive to create) recordset object for
> each row processed.

> How to Use Lookups in DTS
> http://www.sqldts.com/default.aspx?6,107,277,7,1

Thanks a mil Darren! I was hoping I'd get an answer from you!
I never actually knew what Lookups were before, now they're becoming
extremely useful to me!
Cheers,
Bron
 
 
 

1. Can ActiveX Script task reference a Db connection

I have a simple DTS package which creates a connection to
a SQL2000 database, executes an SQL task and the finishes
by executing an ActiveX script task.

The ActiveX script task needs to connect to the same
database as the db connection in the designer.

Can the script reference the connection? At the moment my
script is creating another ADO connection and hard coding
the connection properties.

Thanks,

Mike.

2. calling a stored procedure from i4gl (RDS) (fglpc -V = "Version 7.20.UD2")

3. ActiveX Script Text box missing from ActiveX Script Task Properties

4. ODBC from MS Access 2.0 to Sybase 10

5. reference an excel file from an ActiveX script inside the same package

6. Keeping files open in Clipper

7. Reference a connection in DTS activeX

8. Info needed about Praxis-Omni-Replicator

9. activex scripts and linked txtfileservers

10. Getting the Connection object from ActiveX script

11. Connection timeout in ActiveX script

12. ActiveX Script to specify different file name as source connection

13. DTS - Changing Connection properties from ActiveX script