Alaska Cowboy
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Script task sounds good, I'll have a look at that . . .
where do I find precedent constraint ?
where do I find precedent constraint ?
ASKER
ok, that scripting . . . I'm going to need a little help with that . . .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, just what I was looking for, thank you. I'll give it a shot.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PatelAlpesh, sorry, I missed your comment. I will have to check with SSIS admin if they are interested in installing this task.
ASKER
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("I nputFile") .Value)
here's your code again:
I think the only thing I need to change is to use "file" instead of "files" for clarity, then say "GetFiles(Dts.Variables("I
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;
}
ASKER
also, I am using VB scripting . . .
ASKER
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.
Dts.Variables("FileExists"
I need to get your code in VB language, will try and poke around on this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yechan, great ! thank you.
ASKER
I got it to work with this simple command:
If My.Computer.FileSystem.Fil eExists(Dt s.Variable s("InputFi le").Value ) Then
Dts.Variables("FileExists" ).Value = True
Else
Dts.Variables("FileExists" ).Value = False
End If
so, success !
If My.Computer.FileSystem.Fil
Dts.Variables("FileExists"
Else
Dts.Variables("FileExists"
End If
so, success !
Awesome!!!! Glad to read that you got it to work.
ASKER
very helpful
ASKER
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 . . . )