Create ADO Recordset in DTS VBScript

[Julie Pemberton]

Hi,

In an ActiveX VBScript I need to be able to create a
recordset in code.

Normally, I would use the ExecuteSQLTask, load it into a
Global Variable and set rs = DTSGlobalVariables
("gvRS").Value and then use the recordset. This won't
work because the SQL I want to execute is "SELECT
SeqNumber, CompanyNumber FROM ?" The execute SQL task
will only allow an input variable from the where clause,
it will not accept a ? as a TableName which is what I need
to pass to it.

I have successfully created a custom DTSexecuteSQL:
Set oSQLTask=oPackage.Tasks.New ("DTSExecuteSQLTask")

And successfully executed SQL in Tables using
the "oPackage.Steps("AutoSQLExecute").Execute"

If I create the SQL and try to "set rs = oPackage.Steps
("AutoSQLExecute").Execute" It gives me an error.

Can anyone tell me how to create and ADO Recordset in
VBScript that will work in a DTS ActiveXScript?

Thanks!

[JFB]

Hi Julie
Here is an example:
JFB :)

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

Function Main()
Dim countr
' Connection to SQL
set mySourceConn=CreateObject("ADODB.Connection")
set mySourceRecordset=CreateObject("ADODB.Recordset")
mySourceConn.Open="Provider=SQLOLEDB;Trusted_Connection=yes; Data
Source=ServerName; Initial Catalog=Northwind; user id='user';
password='pwd'"

mySQLCmdText= "select orderID from orders"
mySourceRecordset.open mySQLCmdText, mySourceConn
msgbox mySQLCmdText
msgbox mySourceRecordset.RecordCount

If IsNull(mySourceRecordset.EOF) or (mySourceRecordset.cachesize < 1)
Then
num=1
Else
For countr =1 to mySourceRecordset.RecordCount
num=mySourceRecordset.Fields("orderID").value
mySourceRecordset.MoveNext
Next

End if
mySourceRecordset.close
Main = DTSTaskExecResult_Success
End Function

"Julie Pemberton" <JPemb...@Prac.Com> wrote in message
news:04b001c37def$fe1ac990$3101...@phx.gbl...

[Julie Pemberton]

This worked great.

Thanks!

>.
>

[Darren Green]

In article <04b001c37def$fe1ac990$3101...@phx.gbl>, Julie Pemberton
<JPemb...@Prac.Com> writes

You can still use the Execute SQL Task, by manipulating the SQL outside
of the task and the parameters function, using an ActiveX Script Task.

I like this method as you only need DTS connections as opposed to
maintaining connection properties into multiple places, but if it works
for you, go with what you have from JFB-

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org