February 24, 2011 at 2:01 am
I got a SSIS package which loads the loadfiles placed in filesystem c:\Client Data\LoadFiles\ to SQL Server table TransactionData. I would like to automate the package such that as soon as client places loadfile in said folder, package should be triggered for execution. I dont need anyone to explicitly execution package as file is availability. Overall, package to be executed in very next minute in seconds as soon as loadfile is placed in the said folder.
Any approach would be appreciated
February 24, 2011 at 2:41 am
The File Watcher Task may be of interest.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2011 at 2:54 am
Hi
You need to schedule your SSIS package to run every 5 or 10 minutes.
Hope this is gonna help you
Incase any query let me know
Thanks and Regards
Rashmi
February 24, 2011 at 3:42 am
rashmiptl22 (2/24/2011)
HiYou need to schedule your SSIS package to run every 5 or 10 minutes.
Hope this is gonna help you
Incase any query let me know
Thanks and Regards
Rashmi
That is quite an ineffective solution. Wouldn't it be easier to use the File Watcher task as Phil mentioned, or to use WMI events?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 4:21 am
Hi
I don't think so.Using File watcher task or WMI eventwatcher task in the SSIS package can be usefull but it will not meet the requirement.The requirement is that the SSIS package should run as soon as a file is placed in the source folder and the above task will be executed only if the package runs.
Don't u think the package should be scheduled to run every 5 or 10 minutes to meet the requirement.
February 24, 2011 at 4:25 am
rashmiptl22 (2/24/2011)
Hi--
Don't u think the package should be scheduled to run every 5 or 10 minutes to meet the requirement.
No.
The requirement: ".. very next minute..."
Your solution misses that by up to 9 minutes.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2011 at 4:27 am
rashmiptl22 (2/24/2011)
HiI don't think so.Using File watcher task or WMI eventwatcher task in the SSIS package can be usefull but it will not meet the requirement.The requirement is that the SSIS package should run as soon as a file is placed in the source folder and the above task will be executed only if the package runs.
Don't u think the package should be scheduled to run every 5 or 10 minutes to meet the requirement.
If you schedule the package at a 10 minute interval, and let's say it runs at 12:30. But the file is placed in the folder at 12:31. The file will not be picked up for another 9 minutes. How does that satisfy the requirement that the package should run as soon as possible?
If you start the package when the server starts, a File Watcher Task will immediately notice that the file is present, thus satisfying the requirements. That's what it is build for.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 4:29 am
Phil Parkin (2/24/2011)
rashmiptl22 (2/24/2011)
Hi--
Don't u think the package should be scheduled to run every 5 or 10 minutes to meet the requirement.
No.
The requirement: ".. very next minute..."
Your solution misses that by up to 9 minutes.
Ah, you've beaten me to it.
I type too much 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 6:20 am
Hi
If you start the package when the server starts,
let's say it runs at 12:30 and ends after 5 minute .
But the file is placed at 12:36.When the file will be picked up?
To read the file u need to
run the SSIS package again.
But if the package is scheduled to run every 5 minutes,
the package will run at 12:30 and then 12:35 etc.
Don't you think it will satisfy the requirment
February 24, 2011 at 6:22 am
rashmiptl22 (2/24/2011)
HiIf you start the package when the server starts,
let's say it runs at 12:30 and ends after 5 minute .
But the file is placed at 12:36.When the file will be picked up?
To read the file u need to
run the SSIS package again.
But if the package is scheduled to run every 5 minutes,
the package will run at 12:30 and then 12:35 etc.
Don't you think it will satisfy the requirment
Why would it stop after 5 minutes?
The File Watcher Task will keep running until the file shows up.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 6:49 am
Hi
I agree with you ,The File Watcher Task will keep running until the file shows up.
But let's say 2 files are to be read.The package starts and reads the 2 files and stops.
What if a new file is placed on the source folder ,Again you need to run the SSIS package
February 24, 2011 at 6:52 am
rashmiptl22 (2/24/2011)
HiI agree with you ,The File Watcher Task will keep running until the file shows up.
But let's say 2 files are to be read.The package starts and reads the 2 files and stops.
What if a new file is placed on the source folder ,Again you need to run the SSIS package
It really isn't hard to build something in the package to start it up again after the files have been read.
An Execute Package Task referencing itself should already be enough.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 7:11 am
hi
But how the Filewatcher task will come to know that a new file is placed in the source folder .Once the package stops ,you need to run the SSIS package again to execute the filewatcher task.
let's say scenario is
2 files are to be read in source folder
package starts at 12:30,filewatcher task reads the 2 file and uploads the file.then finish executing all the task and stops at 12:35 .
What if a file is placed at 12:36.How file watcher task will know about the presence of a new file in source folder.
Where the execute package task will come into picture?
February 24, 2011 at 7:20 am
rashmiptl22 (2/24/2011)
hiBut how the Filewatcher task will come to know that a new file is placed in the source folder .Once the package stops ,you need to run the SSIS package again to execute the filewatcher task.
let's say scenario is
2 files are to be read in source folder
package starts at 12:30,filewatcher task reads the 2 file and uploads the file.then finish executing all the task and stops at 12:35 .
What if a file is placed at 12:36.How file watcher task will know about the presence of a new file in source folder.
Where the execute package task will come into picture?
From the description of the File Watcher Task:
It can also be set to look for existing files first
http://www.sqlis.com/post/file-watcher-task.aspx
Did you do at least some research before you try to criticize something?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 7:37 am
rashmiptl22 (2/24/2011)
hi--
Where the execute package task will come into picture?
I do not understand this question.
You seem keen on defending your solution to the death, when we have clearly pointed out its flaws already. Perhaps, rather than negatively and naively assessing the solution suggested, you would present an alternative solution which does meet the requirements and we will congratulate you accordingly.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply