Post reply

Run the Package if file aviliable

  • February 21, 2014 at 4:42 am

    #285995

    Hi,

    I need help on below scenario.

    My source is flat file. I am pulling all data from this file into my table and archive source file thats it. I am doing that no problem.

    But frequecncy of file is not consistent. User want run the package immediatly when file found at source.I am facing this issue. How to identify immediatly file comes at source location and then run the job?

    Please help.

    Thanks

    Abhas.

  • February 21, 2014 at 5:08 am

    #1691058

    Abhas

    You're going to need another job that polls the file folder at regular intervals. You can use xp_fileexist (undocumented, not sure that that's the correct name) or write your own script to do it. Alternatively, change your package so that it runs and does the polling itself. When it find the file, it can move on to the processing part.

    John

  • February 21, 2014 at 7:22 am

    #1691093

    Following up on what John said, you can use a script task inside a For Loop in SSIS to poll for the file's existence. The only quesiton is how quickly do you want this done? If the poll runs every 10 minutes, is that too slow? Should it run every 1 minute, or 30 seconds?

  • February 23, 2014 at 10:19 pm

    #1691408

    Hi John and Daniel , Thanks for your reply.

    I need execute daily once only as soon as file aviliable for that location.

    i.e. The file will be processed once in a day by automation( i.e. as soon as file is placed in source location). On successful execution it will stop polling for that day and start again next day morning

    Thanks

    Abhas

  • February 23, 2014 at 11:41 pm

    #1691413

    I think the suggestions which you got from John and Daniel are good but..

    who do you know full data file is available on source location? suppose we have 10000 records in file and half of the records are copied and rest are left.

    So i would suggest you to generate marker file at end of the file load(once the file is fully loaded) than your process should trigger on

  • February 24, 2014 at 1:04 am

    #1691426

    Thanks Sushil,

    But how job/Package will execute? we need to give interval for run packages as small as possible rite. E.g. 1 minute or 2 minute. Correct?

    Thanks

    Abhas.

  • February 24, 2014 at 1:18 am

    #1691428

    Yes... I think 1 or 2 min will be too short (if you don’t know when they will upload the file) and again it’s depending on your requirement how soon you want to move your data to warehouse. But I think we should trigger after 15 min or 30, it will give sufficient time to upload new file and it will have less logging/run.

  • February 24, 2014 at 3:48 am

    #1691452

    Hi All,

    Thanks for your replies

    Regards

    Abhas

  • February 25, 2014 at 8:44 am

    #1691934

    You can also use the File Watcher Task on SQLIS.com if you are willing to go that route:

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

  • February 25, 2014 at 11:31 am

    #1692026

    You can also do it with a script task

    using System;

    using System.Data;

    using Microsoft.SqlServer.Dts.Runtime;

    using System.Windows.Forms;

    using System.IO;

    using System.Security;

    namespace ST_2da1df7a40444805b38756f999ec1765.csproj

    {

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

    static bool Done = false;

    public void Main()

    {

    // TODO: Add your code here

    string Path = "c:\\temp";

    System.IO.FileSystemWatcher FW = new System.IO.FileSystemWatcher();

    FW.Path = Path;

    FW.Created += new FileSystemEventHandler(FW_Created);

    FW.EnableRaisingEvents = true;

    while (Done == false)

    { System.Threading.Thread.Sleep(1000); }

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    public static void FW_Created(object sender,System.IO.FileSystemEventArgs e)

    {

    Done = true;

    }

    }

    }

  • February 25, 2014 at 11:40 am

    #1692031

    You could run your package and use a loop in it to check for the existance of the files.

    You need the following namespace

    Imports System.IO

    Public Sub Main()

    While Dts.Variables("FileExists").Value = False

    Dts.Variables("FileExists").Value = File.Exists(Dts.Variables("FileFullPath").Value)

    Dts.TaskResult = ScriptResults.Success

    If Dts.Variables("FileExists").Value = False Then

    Threading.Thread.Sleep(300000) '5 minutes

    End If

    End While

    End Sub

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 11 posts - 1 through 10 (of 10 total)

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