Results 1 to 4 of 4

Thread: Help with code

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: 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

    Lystra

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You 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 it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If that was THAT easy the error would have referenced @ftpcommandfile parameter, not @filename.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2004
    Posts
    191
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •