Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to check whether the file exists or not in the respective path using ssis?

Answered How to check whether the file exists or not in the respective path using ssis?

  • Thursday, January 17, 2013 4:26 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I have scenario,where I have shared path in which customers will keeps the daily file.Now my requirement is I should know whether the file exists in the respective folder or not.I have to do that through ssis package.If the file exists or not i should get the mail saying that

    "File exists or file doesn't exists."

    Regards,

    Sudha

    sudha

     

All Replies

  • Thursday, January 17, 2013 4:45 AM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi Laxmi 

    as per my understanding of your query ; with the help of vb.net code/C# code  we can find out where particular object exist or not in the folder , drive or any remote location. you can use script component or script task for the same

    Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)

    • Proposed As Answer by Zdenek Nosek Thursday, January 17, 2013 11:03 AM
    •  
     
  • Thursday, January 17, 2013 4:55 AM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks for your fast reply.I am not aware of using script task or script component.Could you guide me step by step how to perform this task.

    Regards,

    Sudha

    sudha

     
  • Thursday, January 17, 2013 4:55 AM
     
     
    Sign In to Vote
    0

    Hi Lakshmi,

    Declare a variable for FileName and use a ForEachLoop container and assign that variable to get the fileName by pointing to tyhe shared folder as given to u by the customer.Using that variable chk whether the fileName has any value or not.Its some thing like as below

    @FileName declare as variable

    FOR EACH LOOP CONTAINER

    |

    |(Precidence constraint select for Expression and give it as

    @FileName== '' which shud lead to SEND MAIL TASK which sends a mail saying "File Not Exists" ) and the other o/p u can have @FileName !='' and this shud lead to a send mail task saying "File Exits and continue with the file process.)

    Let me know if it helps u.

    Please have look on the comment

     
  • Thursday, January 17, 2013 11:03 AM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi,

    For script task just try a little bit googling.

    http://www.bidn.com/blogs/DevinKnight/ssis/76/does-file-exist-check-in-ssis

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

     
  • Thursday, January 17, 2013 11:26 AM
     
     
    Sign In to Vote
    0

    Hi Lakshmi,

    Refer Code below. I can assist more when I come to know actual requirement.

    DECLARE @directoryPathorFileName VARCHAR(500)

    CREATE TABLE #temp

    ( fileList VARCHAR(500)

    )

    INSERT INTO #temp (fileList)

    EXEC XP_CMDSHELL  'DIR /B '

     

    SELECT fileList AS FileNames FROM #temp

     

    SELECT fileList AS FileNames FROM #temp

    WHERE fileList LIKE '%FileNameToBeSearch%'

    -- Use SQL join or Compare/WHERE clause

    Other hand you can play with .Net FileStram objects. Since they have implicit methods to check files.

    Thanks

    Naveen Raikwar

     
  • Thursday, January 17, 2013 11:53 AM
     
     
    Sign In to Vote
    0

    Hi Lakshmi,

    I just saw an example in a link. As per this example, if file does not exist then 'File doesn't exist' is saved in the database. From database you can use 'send email Task' and you can send email to required users.

    Below is the link:

    http://sql-articles.com/articles/bi/file-exists-check-in-ssis/

    I think this helps you...

    Thanks

     
  • Thursday, January 17, 2013 12:49 PM
     
     
    Sign In to Vote
    0

    Hello,

    What you can do is first calculate the name of the file, this wont be difficult since you already know what the name is going to be.

    Post that, pass this as the remote path to your FTP Task, this you can do by mapping the name of the file to a user defined variable which you will then pass to the FTP Task.

    Once the FTP Task does not find this it will fail and you can then create two send mail tasks or you can set up two script tasks to send mails, one for failure and one when the FTP Task succeeds.

    Please let me know if this works for you.

    Regards,

    Saket Kale.

     
  • Friday, January 18, 2013 11:48 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have tried the above way but whenever the file exists  or not I am getting mail as file exists.

    Regards,

    Sudha

    sudha

     
  • Friday, January 18, 2013 12:52 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0
    An other option is the free/custom File Property Task that has an option for file existance.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed As Answer by sudipta.2.ghosh Thursday, January 24, 2013 7:08 AM
    •  
     
  • Friday, January 18, 2013 12:56 PM
     
     
    Sign In to Vote
    0

    Hi,

    I haven't found this kind of task in my toolbox,we are using SQLIntegrationservices2008R2.Could you let me know where to find this task.

    Regards,

    Sudha

    sudha

     
  • Friday, January 18, 2013 1:11 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    I haven't found this kind of task in my toolbox,we are using SQLIntegrationservices2008R2.Could you let me know where to find this task.

    Regards,

    Sudha

    sudha


    Click on the link in my previous reply... it's a custom task! You have to install it.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Monday, January 21, 2013 5:34 AM
     
     
    Sign In to Vote
    0

    HI Santosh,

    Can you send me screenshots as per ur guideness.I have done the same as above using for each loop,but I am not able to complete the task perfectly.

    REgards,

    Sudha

    sudha

     
  • Monday, January 21, 2013 5:39 AM
     
     
    Sign In to Vote
    0

    Hey Lakshmi,

    Will definetly send u those

    Please have look on the comment

     
  • Monday, January 21, 2013 5:43 AM
     
     
    Sign In to Vote
    0

    HI,

    Thanks for ur qucik response.Could you send those ASAP,since it is bit urgent req for us.

    Regards,

    sudha

     
  • Monday, January 21, 2013 6:21 AM
     
     
    Sign In to Vote
    0

    Hey Sudha,

    I have declared a variable as FileName and i have mapped in for Each loop container. Please have a look on screen shots and let me know if u need more info.

    Please have look on the comment

     
  • Wednesday, January 23, 2013 5:55 AM
     
     
    Sign In to Vote
    0

    Hi All,

    Above answer solved my requirement to some extent,but now the requirement has extended in such a way that a folder contains three files whenever any one of the file  doesn't exists then I should send notification that particular file doesnt exists in the respective path.

    Can any one guide me on this how to perform this task ASAP............

    Regards,

    sudha

     
  • Wednesday, January 23, 2013 6:12 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi All,

    Above answer solved my requirement to some extent,but now the requirement has extended in such a way that a folder contains three files whenever any one of the file  doesn't exists then I should send notification that particular file doesnt exists in the respective path.

    Can any one guide me on this how to perform this task ASAP............

    Regards,

    sudha


    create three task to check each of the three files and connect them all to the send mail task with an OR in the Precedence Constraints... now if on of them reaches the mail task then it will mail...

    Or use one Script Task to it...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Wednesday, January 23, 2013 6:32 AM
     
     
    Sign In to Vote
    0

    Hey sudha,

    I am not sure which of the answers has actually helped, But if u r going as per my reply then please change the value for precidenece constraints.

    1> Inside for each Loop : @FileName == "FileName1.<Ext>" || @FileName == "FileName2.<Ext>" ||@FileName == "FileName3.<Ext>" 

    2> Outside for Each loop: @FileName == "" Or @FileName != "FileName1.<Ext>" || @FileName != "FileName2.<Ext>" ||@FileName != "FileName3.<Ext>" 

    Please have look on the comment

     
  • Wednesday, January 23, 2013 8:37 AM
     
     
    Sign In to Vote
    0

    Hi Santosh,

    I have tried above way by changing expression in precedence constraints,but no luck.I am not able to get required mail.

    Regards

    sudha

     
  • Wednesday, January 23, 2013 8:45 AM
     
     
    Sign In to Vote
    0

    Hey sorry,

    I guess i did not read the req proper it is like if any 1 file is missing out three it mail right.

    Please have look on the comment

     
  • Wednesday, January 23, 2013 9:12 AM
     
     
    Sign In to Vote
    0
    Yes.I should get mail saying that file 1 is missing or file2 is missing and so on.............

    sudha

     
  • Thursday, January 24, 2013 8:28 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hey Sudha,

    I have pasted few image which may help and also the scripts that i have used for same scenario.Please have a loon on that and let me know.

    Query Used For Append

    If ( IsNull(?,'') != '')
If (? In ('File1.txt','File2.txt','File3.txt'))
Select ? = ?+','+?

    Query Used For Excute Sql Task after Foreach loop

    Declare @String Varchar(Max)  ,
		@Xml  Xml, 
		@S	  Nvarchar(Max)	
		
Select @String =  Stuff(?,1,1,'')
Set  @Xml = N'<root><r>' + replace(@String,',','</r><r>') + '</r></root>'

Select ? = Stuff((
Select ','+Coalesce(F.New,F1.Data) 
	From (Select 'File1.txt' As New Union All Select 'File2.txt'Union All Select 'File3.txt' )F
Left Join (	
Select Data 
  From
	(
	Select Val.value('.','varchar(150)') As Data
	  From @Xml.nodes('//root/r') As A(Val)
	) Fin
) F1 On F.New = F1.Data
Where F1.Data Is Null
For Xml Path ('')),1,1,'')

    Presidence constarint between Exequte Sql task and File Not Exits

    @FileNameConCat != "" ||  @FileName == ""

    Presidence constarint between Exequte Sql task and File Exits

    @FileNameConCat == "" ||  @FileName != ""

    Belwo are the images of transformations and the mappings 

    Please have look on the comment