Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

vbscript for dtspackage Expand / Collapse
Author
Message
Posted Monday, September 22, 2008 8:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
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.
Post #573579
Posted Monday, September 22, 2008 9:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, Visits: 915
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...
Post #573591
Posted Monday, September 22, 2008 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
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.
Post #573632
Posted Monday, September 22, 2008 9:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, Visits: 915
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...
Post #573643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse