SQL Server Performance

Check file exists before load into Database

Discussion in 'SQL Server 2005 Integration Services' started by sonnysingh, May 3, 2007.

  1. sonnysingh Member

    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
  2. Jack Vamvas Member

  3. sonnysingh Member

    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...
  4. MohammedU New Member

    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.
  5. sonnysingh Member

    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
  6. satya Moderator

    Do you have a notification services installed or database mail configuration on this server instance?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. sonnysingh Member

    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,
  8. satya Moderator

  9. sonnysingh Member

    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
  10. sonnysingh Member

    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
  11. satya Moderator

    See thsihttp://www.sqldts.com/211.aspx is any help.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  12. sonnysingh Member

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

    sonny
  13. satya Moderator

    DTS still have support for such legacy stuff, I can work it out at my end.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  14. sonnysingh Member

    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..
  15. Madhivanan Moderator

  16. sonnysingh Member

    Thanks Madhivanan

    But I am using SSIS and want to use SSIS..
  17. ekb18c New Member

    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.

Share This Page