check if object exists

Last Post 08 Sep 2011 07:01 AM by russellb. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dbandee
New Member
New Member

--
21 Apr 2011 08:02 AM
hi
i have ftp task that grabs files from remote server (server not on our domain, third party) and processes it locally once it is here.
When there are no files to copy SSIS fails and i log failure.
I would like to check if file is there first so my ftp does not fail
can someone suggest a way of doing it.
thanksv
BTTURNER
New Member
New Member

--
07 Sep 2011 10:33 AM
Hi - I have not architected this for FTP, but here is a start for you. This Proc recieves 1 parameter, a filename. Then checks for the existance of that filename and retunrs 0 if found --and-- -1 if not found. This may get u stared:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create proc spr_filename_check @FileName varchar(255)
AS
-- ***************************************************************************
-- Stored Procedure Name: spr_filename_check
-- Date Created: 2003/10/24
-- By: Bill Turner
--
-- Purpose: This procedure will determine the existance (or non-existance of a DOS file).
-- Error Codes & Meaning: N/A
-- Variable Names & Meanings:
--
-- Input Parameters: DOS file name
--
-- Output Parameters: return code of 0 or -1
--
-- Called By:
-- Calls:
--
-- Updated:
-- By: On: Reason:
-- Bill Turner 2003/10/24 Created
--
-- ***************************************************************************
declare @command varchar(255)

--Prime variable w/ DOS "dir" command
select @command = 'dir ' + @FileName

--Create temp table to hold results
create table #DirResults (DirOutput varchar(500) null)

--Insert results of DOS "dir" command
insert #DirResults
exec master.dbo.xp_cmdshell @command

--Validate existance or non-existance of file name by looking for keyword "bytes"
if exists (select * from #DirResults where DirOutput like '%bytes%')
return (0)
else
return (-1)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
russellb
New Member
New Member

--
08 Sep 2011 07:01 AM
BTTURNER, you can just use xp_fileexists for that.

dbandee, use a workflow constraint to gracefully exit ir f no file exists. Your get command shouldn't raise an error if the file doesn't exist. Or do an ls first and check for the existance of the file b4 issuing your get.
You are not authorized to post a reply.

Acceptable Use Policy