Post reply

Pooling for files

  • April 8, 2009 at 4:13 am

    #384319

    Hi

    I see that there are a few posts with the same sort of questions but not exactly what I’m looking for.

    I get about 50 files FTP’ed to my Windows 2003 server, Monday to Friday, and currently I’m loading them manually (still in testing phase). For the loading I’m using SSIS.

    Part of these 50 files, are Meta data files. I’ll check to see if File1.Meta is in the directory and that way I know that File1.Txt is there already (FTP takes care of that). This works in manual mode as expected.

    The data files can arrive any time from 12 at night to 7 or later even in the morning. I would like to keep pooling (say every 5 mins or so), for the files and when found start the SSIS jobs and if possible to wait at least 20 hours before start pooling again.

    Automating this now is a bit of a problem. I see that SSIS might have a problem running in infinite loops, and was therefore thinking of using Windows Scripting to check for the files and load the data via SSIS.

    What would be the best option which I can implement the easiest as well and that I can be assured on that the task will always be up and running? Also don’t want to waste resources to keep something running like SSIS which might have a memory problem.

    So,

    1)Use Windows Schedule Task (I’ve never used this before)

    2)Use Windows Scripting (Used a little bit, but still need an example or 2, if possible)

    3)Use SSIS and maybe run into memory problems?

    Any suggestions are most welcome

  • April 8, 2009 at 5:28 am

    #974166

    Hi

    I had a similar scenerio - where files were being created every few seconds.

    The solution I used was to create a Windows Service, which utilised FileSystemWatcher component, which watched for the arrival of files in a folder. On arrival of a file the service runs the ssis package.

    The resources\examples I used were:

    For the Windows Service\ FileSystemWatcher-

    http://msdn.microsoft.com/en-gb/magazine/cc301845.aspx

    Executing a ssis package-

    http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx

    Hope this helps

    -Matt

  • April 8, 2009 at 5:40 am

    #974170

    thansk for the info, Matt. Will check it out

  • June 23, 2009 at 8:09 am

    #1013962

    Just to add to my original post, currently using SQL Server Agent, and working really well for the past 3 weeks or so.

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

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