SQL Statement in ActivexScript task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL Statement in ActivexScript task

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-05-22 : 09:48:13

Hi Folks,
I need to be able to execute a SELECT Statement on a Database from the activexscript task in DTS. I want to store the result set of the select statement in a variable. The result set is a count of the number of rows in a table. Is there a way to do this?

Ramdas

Ramdas Narayanan
SQL Server DBA

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-22 : 09:53:37

Hi Ramdas!

The only way I can think of is to use a Dynamic Properties task to update the ActiveX script from a query. Kind of like dynamic SQL... :)

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-22 : 10:29:50

You can do it, but you need to log on within the ActiveX script, which you may consider a security risk.

This is pretty much straight from the MSDN article. It checks a count and determines if the next task should run or not... (Thinking about it this could probably use a myrecordset.close.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

dim myConn
dim myRecordset
dim iRowCount

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the database

myConn.Open = "Provider=SQLOLEDB.1;Data Source=yourserver; Initial Catalog=yourdb;user id = 'user';password='pass'"

mySQLCmdText = "Select 'rowcount' = Count(*) from yourtable"

myRecordset.Open mySQLCmdText, myConn

set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")

If iRowCount.Value > 0 then
Main = DTSStepScriptResult_dontExecuteTask
Else
Main = DTSStepScriptResult_ExecuteTask
End If

End Function

-------
Moo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-22 : 10:37:21

ramdas,

Is this a table that's part of your schema and you want to know the number of rows the table has?

If so you can get this out of the system tables.

you can use mr_mists example but store the values in the global variables of the package.
DTSGlobalVariables("VariableName").Value
so you can access them from VB if that's what you're trying to do.

Check out sqldts.com they have need samples of things you can do in dts activex scripts.

ramdas
Posting Yak Master

181 Posts

Posted - 2003-05-22 : 11:13:52

Thank you folks for your insight.

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources