Check file exists before load into Database

Hi Folks

I am creating SSIS package to load data into specific table. Before load the file I have to check if current date file is exists in the destination folder. how can do it using SSIS?

Thanks...
sonny

Use system.io.file.exist(PathOfFile)


___________________________________
Need an IT job? -http://www.ITjobfeed.com

Thanks....

I have written this piece of code to check file exists..and added into Execute SQL Task. what need to do is
"if @fileexts =1 then execute next step (Execute SQL Task) to stsrt loading the excel file into table or if @fileexts = 0 then send email for non exists of file". how can execute next step in the basis of variable value? I need help on this stage.

here it is the code......


DECLARE @filename VARCHAR(100)
DECLARE@fileexists INT
DECLARE @processname VARCHAR(50)
DECLARE @filedest VARCHAR(100)
DECLARE @month char(2)
DECLARE @date char(2)
DECLARE @year char(4)

SET @processname = 'sales'
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = '0'+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = '0'+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate())

set @filename = @processname+@year+@month+@date
set @filedest = 'D:sales'+@filename+'.xls'

EXEC master..xp_fileexist @filedest,@fileexists OUT
IF @fileexists = 1



Another solution I have found was : File Watcher Task from this site
http://www.sqlis.com/23.aspx
I did looked into but somehow couldn't figured out that how this could solve my problem?

Thanks in advance...

The simple way is when @fileexts = 0 raise the error which will cause the task to fail then on failure send the notification...
@fileexts = 1 do nothing... on success load the excel file into table...

You can also use OPENROWSET or OPENQUERY to load the excel into a table....

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thanks MohammedU

But that's what I do not know that how I added "@fileexts = 0 raise the error " in SSIS. I can think of that in this case go to step so on so...(step that send the notification mail).. could you give some example???

sonny

I have databse mail services installed.. as i am new user of SQL 2005...do not know how configure and use notification services yet..

Thanks,

I mentioned earlier that I have alreday done this (send mail using database mail) part.. what i am asking that execute next task on the basis of step in which checking file exists or not. And if not exist then the execute step send mail to notify that file not exists otherwise the execute step to load the file into table. All this depend on return value of step on which check exists of file which return '0' (file not found) or '1' (file found).

quote:
I have written this piece of code to check file exists..and added into Execute SQL Task. what need to do is
"if @fileexts =1 then execute next step (Execute SQL Task) to stsrt loading the excel file into table or if @fileexts = 0 then send email for non exists of file". how can execute next step in the basis of variable value? I need help on this stage.

here it is the code......


DECLARE @filename VARCHAR(100)
DECLARE@fileexists INT
DECLARE @processname VARCHAR(50)
DECLARE @filedest VARCHAR(100)
DECLARE @month char(2)
DECLARE @date char(2)
DECLARE @year char(4)

SET @processname = 'sales'
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = '0'+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = '0'+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate())

set @filename = @processname+@year+@month+@date
set @filedest = 'D:sales'+@filename+'.xls'

EXEC master..xp_fileexist @filedest,@fileexists OUT
IF @fileexists = 1



Another solution I have found was : File Watcher Task from this site
http://www.sqlis.com/23.aspx
I did looked into but somehow couldn't figured out that how this could solve my problem?

Thanks
sonny

Folks let me define the steps and hope some one help me on this..

step1:
Truncate the staging table
step2:
check the existence of requred file on the destination folder(as I used above pasted script).
if file found then next is step 3 otherwise send file not found mail on step 4

step3:
load the file into staging table

Step 4:
Send warning using database mail to concern people

Step5:
move the data from staging table to production table

Step6:
send database mail for successful data load.

I am stucked on step2 part check file and passing the parameter value 0(file not found) or 1 (file found) to the next step... it is urgent guys..

Thanks in advance

I think tht this works with SQL Server 2000??? what about SQL Server 2005?

sonny

Hi Folks

add further to the topic.. There are more 1 sheet in the excel file.. can I load in one go all of the sheets from one excel file? if not then how?

Thanks in Advance..

Thanks Madhivanan

But I am using SSIS and want to use SSIS..

Wait I'm confused.

If you are using SSIS then why not create a data flow task for the import.

If it fails then use sendmail task, tell you that the required file doesn't exist.

If it succeeds then upload into DB.