Link to home
Start Free TrialLog in
Avatar of Alaska Cowboy
Alaska CowboyFlag for United States of America

asked on

Running an SSIS package when a trigger file arrives on Windows Server using Sql*Agent

We are trying to run an SSIS package when a trigger file arrives on Windows Server.

So I need a job to run every 5 minutes, and if it finds my_trigger_file.txt, then run an SSIS package.

What kind of job would this be ? Sql Agent would run the job "check for trigger file", and then Step 1 is to see if the file exists. If it's there, then Step 2 run the SSIS package.

I have little experience with Sql Agent, but mostly looking for - what kind of job polls for the file ? And then if "success", Sql Agent has to know that to run the SSIS package.
Avatar of Alaska Cowboy
Alaska Cowboy
Flag of United States of America image

ASKER

or, how about this . . . .

my SSIS package is set to run every 5 minutes.

the first step of the package is to look for the trigger file. If it's there, continue. If not, exit gracefully.

- or -
just run my package which tries to load my_trigger_file.txt. If it's not there, then exit gracefully (as opposed to specifically looking for it . . . )
ASKER CERTIFIED SOLUTION
Avatar of yechan
yechan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Script task sounds good, I'll have a look at that . . .

where do I find precedent constraint ?
ok, that scripting . . . I'm going to need a little help with that . . .
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok, just what I was looking for, thank you. I'll give it a shot.
SOLUTION
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
PatelAlpesh, sorry, I missed your comment. I will have to check with SSIS admin if they are interested in installing this task.
yechan, I need to check for only one file (with a variable name, which I have working).

I think the only thing I need to change is to use "file" instead of "files" for clarity, then say "GetFiles(Dts.Variables("InputFile").Value)

here's your code again:
string filePath = Dts.Variables["User::cFolderLocation"].Value.ToString();

DirectoryInfo dirInfo = new DirectoryInfo(filePath);

FileInfo[] files = dirInfo.GetFiles("*.zip");

if (files != null && files.Length >= 1)
{
   Dts.Variables["User::vFileExists"].Value = true;
 }
else
{
   Dts.Variables["User::vFileExists"].Value = false;
}

Open in new window

also, I am using VB scripting . . .
Ok, I got the precedence constraint working, but it's hard coded

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

I need to get your code in VB language, will try and poke around on this.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yechan, great ! thank  you.
I got it to work with this simple command:

        If My.Computer.FileSystem.FileExists(Dts.Variables("InputFile").Value) Then

            Dts.Variables("FileExists").Value = True
        Else
            Dts.Variables("FileExists").Value = False
        End If

so, success !
Awesome!!!!  Glad to read that you got it to work.
very helpful