Help with code

I have this code in a DTS package which is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

drop proc VerifyRequest
go
/*
* VerifyRequestTransfer - run a command that looks for @.filename in the output
*

*
* This proc looks for a file matching 'tbl_%' in the output of an ftp command.
* The output message reports success/failure of transfer.
* A return code of 1 indicates success
* Return code = 0 indicates failure.
*
* How it Works:
* ftp is executed using @.ftpcommandfile as input to the -s parameter.
* The output of ftp is written to a table
* The table is cleared of garbage records
* The count of records matching @.filename is checked
* if the count = 1 then there success!
*/
CREATE proc VerifyRequest
@.filename varchar(200),
@.ftpcommandfile varchar(1000)
as

declare @.rc int
declare @.rows int, @.errcode int, @.rows2 int
set @.rc = 0
set @.rows = -9998

set nocount on
-- build a table containing list of files in Request directory
if exists (select * from tempdb.dbo.sysobjects where name='RequestFiles' and type = 'U')
drop table tempdb.dbo.RequestFiles
create table tempdb.dbo.RequestFiles (
line_no int identity(1,1) Primary key clustered,
Filename varchar(200) NULL
)
declare @.cmd varchar(2000)

--Get list of remote files
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
Exec master.dbo.xp_cmdshell @.cmd
select @.rows = @.@.rowcount, @.errcode = @.@.error
if @.rows = 0 OR @.errcode != 0
begin
set @.rc = -1
goto done
end

-- remove non-files and already processed files ( there might have been old files on remotesystem )
Delete
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%tbl_%'
-- check count
select @.rows = (select count(*) from tempdb.dbo.RequestFiles
Where tempdb.dbo.RequestFiles.Filename like '%'+@.filename+'%' )

if @.rows = 1
set @.rc = 1
done:
return @.rc
go

Now the message I am getting is:

The task reported failure on execution. Procedure 'VerifyRequest' expects Parameter '@.filename', which was not supplied.

I don't know where to set this parameter.

I hope someone can help.

Thanks

LystraYou will need to supply both the @.filename and @.ftpcommandfile parameters when you call the procedure from your code:

VerifyRequest 'C:\Yourfile.nam', 'C:\YourCommandFile.nam'|||If that was THAT easy the error would have referenced @.ftpcommandfile parameter, not @.filename.|||THe vb scripts that is first started which is:

Function Main()
DTSGlobalVariables("PostDate") = month(now()) & "/" & day(now()) & "/" & year(now())
dim tmp
dim filename

tmp = right("0" & datepart("m", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & right("0" & datepart("d", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & datepart("yyyy", DTSGlobalVariables("PostDate"))
DTSGlobalVariables("Datestamp") = tmp
filename = DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")
DTSGlobalVariables("TransferFilename") = DTSGlobalVariables("TransferFileDir") & "\" & DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")

' use the output file name to generate an FTP command file
set oFSO = CreateObject("Scripting.FileSystemObject")
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPCommands").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles"
oFile.writeline "mput " & DTSGlobalVariables("TransferFilename").Value
oFile.writeline "quit"
oFile.Close
set oFile = nothing

' Generate an FTP command file to verify that transfer worked.
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPVerifyReq").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles" & vbCRLF & "ls -l " & vbCRLF & "quit"
oFile.Close
set oFile = nothing
set oFSO = nothing

' save the output filename into the transfer verification query
tmp = "Select count(*) from tempdb.dbo.RequestFiles " & vbCRLF & _
"Where tempdb.dbo.RequestFiles.Filename like '%"+ filename + "%'"
'Create a new query to look for files with the output filename
' find the task that counts the number of transferred files
set oTasks = DTSGlobalVariables.Parent.Tasks
for each task in oTasks
if task.Properties("Description") = "Evaluate File Count" then
' set the Query in DynamicProperties Task so that it checks for today's file
For Each oAssignment In task.CustomTask.Assignments
if instr( oAssignment.DestinationPropertyID , "'TransferredFileCount'" ) then
oAssignment.SourceQuerySQL = tmp
end if
next
end if
next
Main = DTSTaskExecResult_Success
End Function

Since I have created a ftp transfer file that lists the file names and should put the files in a temp table. I am having trouble with the ftp command to list the file in my file.

Thanking you in advance.

Lystra