Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-21
16
Medium Priority
?
1,810 Views
Last Modified: 2016-02-11
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
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
16 Comments
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39266660
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
 
LVL 1

Accepted Solution

by:
yechan earned 900 total points
ID: 39266670
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39266793
Script task sounds good, I'll have a look at that . . .

where do I find precedent constraint ?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39266807
ok, that scripting . . . I'm going to need a little help with that . . .
0
 
LVL 1

Assisted Solution

by:yechan
yechan earned 900 total points
ID: 39266841
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39266862
ok, just what I was looking for, thank you. I'll give it a shot.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 100 total points
ID: 39267553
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39272967
PatelAlpesh, sorry, I missed your comment. I will have to check with SSIS admin if they are interested in installing this task.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39278242
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39278245
also, I am using VB scripting . . .
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39278297
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
 
LVL 1

Assisted Solution

by:yechan
yechan earned 900 total points
ID: 39278355
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39278444
yechan, great ! thank  you.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39278662
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
 
LVL 1

Expert Comment

by:yechan
ID: 39278672
Awesome!!!!  Glad to read that you got it to work.
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 39278674
very helpful
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question