I am new to using scripting in DTS.I want to call a stored procedure
using VB script, I happednd to get some code for doing this..
I am reproducing it below....

however had two questons before i implemented it....

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

Function Main()
Dim strFileName, strFailFlag

'Dim adCmdStoredProc, adExecuteNoRecords, adVarChar,
adLongVarChar,adParamInput, strDSN, strServerName, strDatabase,
strLogin, strPassword, objConn, objCmd, conn

' Standard ADO constants...usually put into an include file
adCmdStoredProc = 4
adExecuteNoRecords = 128
adVarChar = 200
adParamInput = 1

strDSN = "DSNName"
strServerName = "ServerName"
strDatabase = "DatabaseName"
strLogin = "sa"
strPassword = ""

' Build the connection string from the variables above.
' This connection string relies on a DSN
conn = "DSN=" & strDSN & ";" & _
"SERVER=" & strServerName & ";" & _
"UID=" & strLogin & ";" & _
"PWD=" & strPassword & ";" & _
"DATABASE=" & strDatabase


'Instantiate objects
Set objConn = CreateObject("ADODB.Connection")
Set objCmd = CreateObject("ADODB.Command")

objConn.Open conn, strLogin, strPassword
With objCmd
.ActiveConnection = conn
.CommandText = "dbo.ap_LogFileSuccess"
.CommandType = adCmdStoredProc
'Add Input Parameters
..Parameters.Append .CreateParameter("@inFailFlag",
adVarChar,adParamInput, 6, CStr(strFailFlag))
..Parameters.Append .CreateParameter("@inFileName",
adVarChar,adParamInput, 255, CStr(strFileName))


'Execute the function
'If not returning a recordset, use the adExecuteNoRecords
parameter

.Execute, , adExecuteNoRecords
' Clean up
If IsObject(objConn) Then Set objConn = Nothing
If IsObject(objCmd) Then Set objCmd = Nothing








End With





End Function


1..Is there a way I can get the connection properties by referencing
the connection object instead of hard coding it....

2.Can i give the execte stored procedure more easily?I am a newbie and
cannot really understand create parameter , I would want to give the
parameters explicitly and also the execure stored procure command...if
somene has a sep by step method for executing stored procedure from vb
script and can send me i would be obliged.


Thanks

Ajay

I would use an ExecuteSQL task and pass the statement in through that. An
EcecuteSQL statement is tied to a connection object and we can change the
connection object's settings as well should we need to.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)

Connections
(http://www.sqldts.com/Default.aspx?101)



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ajay Garg" <ajayz90 (AT) hotmail (DOT) com> wrote