Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1860
  • Last Modified:

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.
0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 11
  • 4
4 Solutions
 
Alaska CowboyAuthor Commented:
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 . . . )
0
 
yechanCommented:
Have you considered using a Script Task that looks for the file?  If the script task finds a file, set a local variable to indicate that it found a file.  Afterwards, modify the "Precedent Constraint" to check for that value.  If true, continue, otherwise set it to false so that no other tasks are running.
0
 
Alaska CowboyAuthor Commented:
Script task sounds good, I'll have a look at that . . .

where do I find precedent constraint ?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Alaska CowboyAuthor Commented:
ok, that scripting . . . I'm going to need a little help with that . . .
0
 
yechanCommented:
Here is the code that I use in my Script tasks to check for the existence of a file:

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


The precedence constraints are the arrows that connect the various tasks.  If you double-click the arrow that connects the Script task with the next task, you'll have to enter the following expression in the expression task:

@[User::vFileExists]==true

So, if the vFileExists variable contains the value "true", it will continue onto the next task, otherwise nothing will happen and the package will go back to sleep.
0
 
Alaska CowboyAuthor Commented:
ok, just what I was looking for, thank you. I'll give it a shot.
0
 
Alpesh PatelAssistant ConsultantCommented:
To do that there is a new task "Filewatcher" (3rd party SSIS task). It works like filesystemwatcher of Windows.

It triggers when file came/update/delete as actions are configured on that folder.

you will get it from here


File watcher task
0
 
Alaska CowboyAuthor Commented:
PatelAlpesh, sorry, I missed your comment. I will have to check with SSIS admin if they are interested in installing this task.
0
 
Alaska CowboyAuthor Commented:
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

0
 
Alaska CowboyAuthor Commented:
also, I am using VB scripting . . .
0
 
Alaska CowboyAuthor Commented:
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.
0
 
yechanCommented:
Hi William,

I don't code in VB.net, however, check this link out.  It will convert C# code to vb.net and vice versa.

http://www.developerfusion.com/tools/convert/csharp-to-vb/
0
 
Alaska CowboyAuthor Commented:
yechan, great ! thank  you.
0
 
Alaska CowboyAuthor Commented:
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 !
0
 
yechanCommented:
Awesome!!!!  Glad to read that you got it to work.
0
 
Alaska CowboyAuthor Commented:
very helpful
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 11
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now