issue with calling stored proc

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

Transact-SQL (2000)

issue with calling stored proc

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-25 : 22:11:01

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-25 : 22:50:30

see
http://www.nigelrivett.net/DTSExecuteStoredProcedure.html

also see
http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
And decide whether you want to use dts for this or not.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-25 : 23:30:26

how should I pass the parameters for the stored procedure.The stored procedure is already created in the database called usp_emp_detail with parameters @emp_number,@file_name,@file_location,@file_extension

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-25 : 23:36:56

see
http://www.nigelrivett.net/DTSExecuteStoredProcedure.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-26 : 14:38:52

How can I get date modified of a file in this DTS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 14:42:24

Use FileSystemObject in VBScript with the DateLastModified property of the File object.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-26 : 14:46:58

Thank tara it works....................