How to execute SSIS package when a file is arrived at folder

Question

Hi,

Can anyone suggest answer for the below query

How can i run a package when a particular file is available in the folder?

Answer 1

Hi,

My suggestion is schedule a job to run every hour. Hope your package will check for file existence as a first step.

Thanks,

Guru

Answer 2

have you considered using a File System Watcher

You can implement this via:

• A scipt code, which will call the package when a file is made available. Check http://msdn.microsoft.com/en-us/library/system.io.filesystemwatcher.aspx
• Using a custom task http://www.sqlis.com/post/file-watcher-task.aspx
• Using WMI Event watcher, where you would have to compose a WMI Query http://msdn.microsoft.com/en-us/library/ms141130.aspx

This is pretty easy to configure.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 3

Hi,

How to check whether the file exist in the particular folder? am using the ftp task to transfer the file from FTP folder to local folder as the first component.

Regards,

Vipin

Answer 4

Aahh...Now I get it. I would have this approach using just one Script Task

• Drop a script task on the designer pane and select a script language of choice and declare any variables, if required
• Establish a FTP session using the .NET classes
• Check is file exists using System.IO library
• If true, get file to local

Note:

There may be alternate approach available.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 5

you can use the file watcher task links below

http://msdn.microsoft.com/en-us/library/ms141130.aspx

This has the query as well which you need to check.

---

Abhinav

Answer 6

Hi,

Place an script task in your package as a first step and follow the below code to check whether file exists on a particular folder. Once it is true then continue your FTP Task. Hope it will helps.

        Dim fl As String
        Dim fl_path As String
        Dim file_exist As Boolean
        Dim file_exists As Boolean
        Dim var As Variables

        Dts.VariableDispenser.LockForRead("Trigger_File_Path")
        Dts.VariableDispenser.LockForRead("Job_Trigger_Path")
        Dts.VariableDispenser.LockForWrite("File_Exists_Flag")
        Dts.VariableDispenser.GetVariables(var)
        fl = var("Job_Trigger_Path").Value + "Job_Trigger.csv"
        file_exist = File.Exists(fl)
        fl_path = var("Trigger_File_Path").Value.ToString()
        file_exists = File.Exists(fl_path)
        If file_exist = True Then
            File.Delete(fl)
        End If
        If file_exists = True Then
            var("File_Exists_Flag").Value = 1
            'File.Delete(fl_path)
        Else
            var("File_Exists_Flag").Value = 0
        End If
        var.Unlock()

Thanks,

Guru

Answer 7

Hi Vipin,

Please, also check following, may be it's helpful -

http://www.sqlis.com/post/file-watcher-task.aspx

Regards,
Santosh

---

http://microsoftbizintel.wordpress.com/

Answer 8

A WMI Event Watcher Task could help you here: http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html

---

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

Answer 9

you can design youe ETL in a way that .....

you won't need to check if the file exist , the loop that is checking the files will have NO file to pick and basically it end the package , a good example is this link  for text files and this link for excel files

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).