FILE WATCHER IN A FOLDER IN SSIS

Scenario

I have a scenario like this in real time. Client will paste the files in a folder. if file is there in a folder then you have to load into database otherwise package should check again for 10 min. Like this it should check every 10 min upto 1 Hour.

1) Job should run only once I.e you cannot schedule the job for every 10 min

Answer:

Step1

 create variables like below.

Step2: take script task  and select read-only variables and read-write variables as below

Step3:

go to edit script and add below code.

   public void Main()
        {
            // TODO: Add your code here
            string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();

           var directory = new DirectoryInfo(SourceFolderPath);
            FileInfo[] files = directory.GetFiles();

            //Declare and initilize variables
         
            int i = 0;
            //Get one Book(Excel file at a time)
             foreach (FileInfo file in files)
            {

                 i = i + 1;
                 Dts.Variables["User::count"].Value = i;
            }

            if (i == 0)
            {
                find();
            }
         }
        public void find()
        {
            int totalsleepcount = (Int32)Dts.Variables["User::totalsleepcount"].Value;
            if (totalsleepcount <= 6)
            {

                // sleep for 10 min
                System.Threading.Thread.Sleep(600000);
                totalsleepcount = totalsleepcount + 1;

                Dts.Variables["User::totalsleepcount"].Value = totalsleepcount;
                Main();
            }
         
        }

Step4:

drag and drop data flow task. this data flow task should run when count of file is greater than zero.
this can be achieved by precedence constrain like below.

thanks

if you want download this package click me