Post reply

Check if File exist

  • November 25, 2009 at 11:56 am

    #135601

    Hi all,

    I was trying to write a t-SQl stmnt to check if a file exist then

    goto DataFlowTask A

    else

    goto Dataflow task B

    Can any one provide me the code for checking if a file exists then proceed....

    thanks in advance.

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 25, 2009 at 12:33 pm

    #1084278

    [font="Comic Sans MS"]

    Dear SQL Learner,

    There can be 2 approaches:

    Option 1:

    You can use a script task to determine if the file exists or not. You may use a variable fileExists to store TRUE/FALSE depending on the condition. From the script task - use the precedence constraint to evaluate the value of fileExists and proceed accordingly.

    Option 2:

    Use ForEach Loop container and store the filename into a string variable say fileJustFound. [ you can see how you need to do this here : http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx ] Now use precedence constraint from the For Each Loop container to evaluate the expression if fileJustFound string lenth > 0 and use this to direct which DFT you want to use.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • November 25, 2009 at 1:40 pm

    #1084334

    [font="Comic Sans MS"]Hi

    thanks for your reply,

    I dont wanna use the Script task, I have code for it.

    I wanna use my Execute SQl task and set a vriable value to 1 or 0 and proceed to the dataflow task using precedence constraint.

    I want to know if this check of file exist or not can be done using T-SQL statement in an execute SQl task

    Thanks[/font]

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 25, 2009 at 7:42 pm

    #1084415

    [font="Comic Sans MS"]

    Yes - this can be done using xp_cmdshell for sure. But that would be far complicated and you might face several issues with permissions etc ..

    May I know the business (or architectural) reason behind avoiding native SSIS capabilities and using t-sql under SSIS to find if a file exists?

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply