issue with calling sp

[SqlHunter]

Here I have Vb script and in the DTS package this the only thing I have with file_name,file_location...etc as the global variables.Now I want to call a stored procedure inside this script.What should I do ?..

Function Main()

Dim fso
Dim f

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("N:\Test FilePath")

For Each file In f.Files

'File Name
'-----------------
file_name= file.Name
'msgbox file_name

'File Location
'---------------------
file_location =file.path
msgbox file_location

'Length of File
'------------------------
split_extension = Split(File.Name, ".", -1, vbBinaryCompare)
length_of_file_name = Len(split_extension(0))
'msgbox length_of_file_name

'File Extension
'------------------------
file_extension =split_extension(1)
'msgbox file_extension

'emp Number
'-----------------------
emp_number = Left( split_extension(0) , 10 )
msgbox loan_number

'Doc Abbreviation
'----------------------------
doc_abbreviation =Right(split_extension(0),length_of_file_name-10)
msgbox doc_abbreviation



Next

Main = DTSTaskExecResult_Success
End Function

 

[gradley]

From within an ActiveX script, I would use an ADODB.Command object to call the stored procedure.

Ex:

Dim objConn
Dim objCmd
Dim lngRecords
Dim strConnectionString

Const adExecuteNoRecords = 128
Const adCmdStoredProc = 4


strConnectionString =  DTSGlobalVariables("strConnectionString").Value   
	
set objConn = CreateObject("ADODB.Connection")
objConn.Open = strConnectionString

Set objCmd = CreateObject("ADODB.Command")

With objCmd
	.CommandText = "Stored Procedure Name"
	.CommandType = adCmdStoredProc
		
	.ActiveConnection = objConn
	Call .Execute(lngRecords, , adExecuteNoRecords)		
			
        Set .ActiveConnection = Nothing
End With


Set objCmd = Nothing
objConn.Close
Set objConn = Nothing