vbscript for dtspackage

  • I want add a activexscript or vbscript in sql agent jobs such that if file in a directory exists then excute second step , if not then end job successful.

  • Hi,

    You don't necessarily need an activeX script in the Agent job. You can do it by using the xp_fileexists extended proc in T-SQL.

    CREATE TABLE #exists (file_exist BIT, is_dir BIT, parent_dir BIT)

    INSERT INTO #exists

    execute master..XP_FILEEXIST '\\MachineName\directory\file.txt'

    IF EXISTS (SELECT * FROM #exists WHERE file_exist = 1)

    RAISERROR('File Exists', 10, 1) -- Non-fatal error as notification

    ELSE

    RAISERROR ('File Does Not Exist', 16, 1) -- Fatal error failing step

    You can then have the step go to step X on pass and step Y on failure.



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • but I don't know the filename or you can say any fillename in directory.

    I know only path upto directory.

    if a file is in directory then execute second step.

    please help.

  • You can use xp_cmdshell to run the DIR command.

    DECLARE @dir VARCHAR(200)

    DECLARE @cmd VARCHAR(205)

    SELECT @dir = '\\machine\dir'

    SELECT @cmd = 'DIR ' + @dir

    CREATE TABLE #directoryFiles (lineID INT, line VARCHAR(2000))

    INSERT INTO #directoryFiles(line)

    EXEC master.dbo.xp_cmdshell @cmd

    SELECT * FROM #directoryFiles

    DROP TABLE #directoryFiles

    This will list all files in a directory, you can then use SUBSTRING to parse out the filenames. Note that the lineID is useful for getting rid of unwanted header and footer lines returned by the DIR command.



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply