| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2008-04-29 : 14:34:41
|
| I am trying to trigger SSIS package if a file is present in a folder, can I use any of the SSIS tasks in doing that??Thanks!! |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-04-29 : 16:41:19
|
| Yes (some options):1) File.Exists(file path) 'Script Task2) ForEach File Enumerator3) File Watcher Task 'http://www.sqlis.com/23.aspx |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-04-29 : 16:45:46
|
| How can I use ForEach File Enumerator, please send me an example I can refer to. Thanks much. |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-04-29 : 16:49:55
|
| Here's one example http://www.sqlis.com/55.aspxorGoogle http://www.google.com/search?hl=en&q=ssis+ForEach+File+Enumerator |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-04-29 : 17:35:11
|
| Thanks for the speedy response, this is really valuable info.Actually here is what I need:As soon as a excel file is present in the source folder, the SSIS package should start executing. The source folder will have either 1 or many excel files as soon as the excel file(s) is present in the folder the SSIS package should execute. |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-04-29 : 17:55:38
|
| You may need File Watcher Task (free download) to kick off your SSIS or have a SSIS package scheduled every so many minutes (resource hog) that kicks off SSIS that processes your file. You can be creative on your solution but either way you design it you need something to watch the files come in. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-04-30 : 15:49:55
|
| Installed File watcher task, then created a new SSIS package by adding the file watcher task in the control flow, below are my setting's inside the file watcher task editor:2. OptionsFilter - *.*Find Existing Files - TrueTimeout warning - False3. PathPath input type: Direct imputPath - \\server1\SourcefolderInclude subdirectories - FalseActually my understanding about this task is:This file watcher task will constantly check for the file and then will execute the SSIS package.If my understanding is correct, then where do we specify the name of the SSIS package?? And also do we need this SSIS package which has the File watcher task to run every 5 minutes or so, so that it can keep checking for the file existence.Please help, thanks!! |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-04-30 : 16:15:25
|
| Have precedence contraint between FWT and Execute Package Task (EPT) where if OutputVariableName not empty then file is present, thus EPT executes. EPT connection can be SQL Server or File System which basically you point to using "Location" section.Also, depending on your flow/logic instead of executing another SSIS package you can execute Data Flow Task (data processing done here). |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-04-30 : 17:51:14
|
| I linked up the File watcher task and the Exec package task(Filesystem - with the desired package that should execute when true). Do I need to schedule this package1 which has FWT and EPT to run continuously so that when it finds the file in the source folder, the package1 will execute?? If there is no file in the folder, the package will keep running(alomost like in hung mode) and only when the file is present then the package will complete execution, and once it completes execution how does it start executing again?Can we do something like this:1. Schedule the package to run say every 5 minutes2. Once the file is present the desired package will execute which is linked to the file watcher task, if not the desired package will not run and the package1 should stop.Thanks much!! |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-05-01 : 12:26:51
|
| Unfortunately, yes. Also, be aware that FWT can cause memory leak and it's been discussed in the SSIS community. It sounds like you can avoid using FWT altogether and perhaps redesign your logic and use ForEach File Enumerator to check if file exists or not. Then you can use SQL Agent to run package every n minutes. Having FWT and SQL job is redundant due its nature of file checking mechanism at certain time interval.Another option you can check is WMI Task which also checks for file existence but again that requires some sort of start and restart of the package. There's also .NET File Watcher option you can research which can also detects file arrival and such. Try different approach and see which one fits best and efficient. Sorry if this does not help much to you. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2008-05-01 : 17:13:30
|
| In order for FWT to check for file existence we need to schedule it to run right? Without scheduling/running can it check for file existence?How can I use For each file enumerator for file existence?Thanks for your responses. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-01 : 17:16:57
|
| Can FWT launch an executable once the file is found? If so, then you should launch the DTS package from FWT. Otherwise you'll need to run the DTS package frequently.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-05-02 : 08:01:04
|
| Solution #1:Correct, FWT needs to be running/scheduled (Package1 job fire every n minutes) and it needs to be restarted once data processing (eg Data Flow) is done. My thought of the restart is to add "Exec SQL Server Agent Job Task" (under Maintenace Plan Tasks) to run the same package. So to simplify these are the task items: FWT > Data Flow > Exec Agent Task. I have not fully tested this flow but worth a try I think.Solution #2 (my personal pick):This is a shortcut to solution #1. Instead of FWT detecting incoming files you use "Foreach File Enumerator" container. In the container set Variable Mappings to a string variable which holds the file location. Within the container you can put your Data Flow/data processor and will automatically loops out once file no longer exists. So to make this work it still needs to be scheduled and run every n minutes, however, the difference is that FWT is not needed since the container will look for the file itself. So the task items: Foreach Loop > Data FlowSetup #1 (Data Flow inside Foreach container) -Variables (Package level), Name = myFile, Data Type = StringVariable Mappings (Foreach Loop), Variable = User::myFile, Index = 0Setup #2 (Data Flow outside Foreach container) -Variables (Package level), Name = myFile, Data Type = StringVariable Mappings (Foreach Loop), Variable = User::myFile, Index = 0Precedence Contraint (between Foreach Loop and Data Flow), Evaluation Expression = Constraint, Expression = @[User::myFile]!="" (proceed if myFile value is not empty)Of course you can use Script task to look for files too (System.IO.File.Exists(myFile) but it's up to you.Hope this helps. |
|
|
|
SSISJoost
Starting Member
9 Posts |
Posted - 2011-03-13 : 16:17:47
|
| Here is a C# Script Task example of checking if a file exists:http://microsoft-ssis.blogspot.com/2011/03/get-file-properties-with-ssis.html |
|
|
|
|
Triggering SSIS package based on a file existence