SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


vbscript for dtspackage


vbscript for dtspackage

Author
Message
bang.prashant
bang.prashant
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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.
Adrian Nichols-360275
Adrian Nichols-360275
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 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...
bang.prashant
bang.prashant
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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.
Adrian Nichols-360275
Adrian Nichols-360275
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search