We have 20 packages that have been scheduled to run every Monday. The source file is Text as well as Excel files. Now, in addition to running on the scheduled time, we wants the package to run whenever they place the source text and excel files in some specific folder.
is it possible in SSIS 2012? Do we have any option? This has to be run both in QA and PROD - so not sure whether we can use some third party tool. However, please suggest me what are the options available..
Thanks in advance!
The solution I typically recommend is writing a Windows Service encompassing the .Net FileWatcher
once the FileWatcher detects a file arrival it logs this even to a table (acting as a queue).
The SSIS package can then be started from within the very Windows Service using a bat or start_process or a SQL Agent job (sp_start_job <name>).
I have implemented two of these, it took half a day to get there the 1st time.
It works better that watching for files in a looping package with a WMI event task.
Arthur My Blog
Great! Can you please tell me the steps for doing that? or any website where i can get the .net code as i am not into .Net code..it would be really great..
1: File Watcher in a Windows Service http://www.codeproject.com/Articles/18521/How-to-implement-a-simple-filewatcher-Windows-serv
Arthur My Blog
You can also have a package which runs indefinitely and use WMI watcher task in this package to wait for the file.
You can fined a sample in this tread:
Another way to approach this problem...
1. Create a Master SSIS package with FOR Each loop.
2.FOR EACH loop will be checking a file location and when there is a file it will execute the child packages to process files.
3.Create a SQL Agent job to execute the master package
4.Schedule the SQL agent job to execute as per business requirement.
--Please note you need to create DB credential and SSIS executor account to invoke SSIS package from SQL agent job.
It can be easily done by using SQL jobs..
All that you need to do is defining a alert after creating a job.
Defining an alert is quite straightforward and can be configured by right clicking on the ‘Alerts’ FOLDER in SQL Server Agent and select ‘New Alerts’
create a WMI Event Alert called Testing Alert with the following query.
The namespace mentioned here similar to the namespace we give in c#.net
The following query picks up any event which is created due to file transfer of Test .txt to the folder c:\TestFolder\
SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘c:\\TestFolder\Test.txt’
Thanks a ton guys for your valuable inputs!
Initially I tried what Arthur mentioned - there are 2 steps
Step 1: I am done till build the project. After that, the article says something like below, which i don't understand as i have never worked on C# anytime
// To install the newly created service, you must use the .NET Framework
InstallUtilprogram. I have included two batch files within this project- one to install the service, and another to UNinstall it. Each can be reused. Simply replace the
PROGvariable with the name of the service that you are installing or uninstalling.//
Running SSIS package programmatically -- this has 5 approaches . Not sure which one to choose.
Can you guys help me to complete the step 1 and step 2?
I tried Kingxxx1's approach. I am using SSIS 2012.
These are the steps i did to understand his approach
1) Created a simple package to load a small text file into a Table.
Source file Folder : C:\Users\vsk\Desktop\TestFolderForFileWatcher
2) Deployed the pkg in the SSISDB catalog
3) created a SQL server job
4) Created a WMI Event Alert and enabled it.
Namespace by default:\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘C:\\Users\\vsk\\Desktop\\TestFolderForFileWatcher\\TestSourceFileForFileWatcher.txt’
I deleted the text file and then put it again, hoping that the package will run after detecting the file. But it is not getting executed.
I do not know anything about .Net, or __InstanceCreationEvent event. Now, can you please tell me whether i am missing anything. Should I write some c# code somewhere to enable smoething.It would be great if you provide me some detailed information!
With the WMI FileWatcher you need to run the package in an infinite loop. Which still leaves some room for a potential to miss the package, like I said, not advocating it to anyone.
I guess the example I gave you is a little hard to follow, how about you watch how to create a Windows Service: http://www.youtube.com/watch?v=uM9o8GsO_u4
You then still can borrow the code from my example that covers the creation of the FileWatcher and then you must be all set with your task.
Arthur My Blog