Scenario:
Sometime we get requirement something like this: Your
Package should check a flag (Cell Value) in table and if it is 1 then run the
remaining tasks in Package OR Check I flag file exists in Folder then run the
remaining Tasks in Package.
Solution:
Let’s say in this scenario, we are considering a third party
software runs on some schedule and once it get completed it create a file in
Folder C:\temp with name ProcessCompleted.txt.
What we have to do, create a package in which the very first
step should be to check if file exists. If file exists then we will run our
other tasks otherwise we can complete our package without running any tasks.
Step 1:
Create three variables in your SSIS Package
FolderPath :: provide
the folder path in which you want to check the file
FileName : : provide the file name
FileExistsFlg :: we will use this to indicate file exists or
not. 1 means exists and 0 means does not exists.
After creating variables, Drag Script Task to the Control Flow Pane and Provide Variables and Finally Click on Edit Script.
Step 3:
Write the code as shown in snapshot, We are checking if the file exits in the folder then we are setting the value of FileExistsFlg variable to 1 else it will remain 0.
Step 4:
As now we have set the value of variable FileExistsFlg, we can use this variable to run our next tasks if file exists (FileExistsFlg=1). Bring Data Flow task in Control Flow Pane and Connect Script Task to it. Double click on Green line ( Precedence Constraint) between two tasks.
Choose Expression and Constraint from Evaluation operation drop down and in Value choose "Success". In expression write @FileExistsFlg==1.
This mean whenever the file will be available in given folder , our package will execute and it will run Data Flow task. If file will not available it will stop after executing Script task. As to run Data file the variable ( FileExistsFlg should be 1.
Final Output
As in snapshot we can see that when the file will be present, the data flow task will run otherwise it will stop at Script task.
Write the code as shown in snapshot, We are checking if the file exits in the folder then we are setting the value of FileExistsFlg variable to 1 else it will remain 0.
Step 4:
As now we have set the value of variable FileExistsFlg, we can use this variable to run our next tasks if file exists (FileExistsFlg=1). Bring Data Flow task in Control Flow Pane and Connect Script Task to it. Double click on Green line ( Precedence Constraint) between two tasks.
Choose Expression and Constraint from Evaluation operation drop down and in Value choose "Success". In expression write @FileExistsFlg==1.
This mean whenever the file will be available in given folder , our package will execute and it will run Data Flow task. If file will not available it will stop after executing Script task. As to run Data file the variable ( FileExistsFlg should be 1.
Final Output
As in snapshot we can see that when the file will be present, the data flow task will run otherwise it will stop at Script task.
i did this last week with different approach but your approach is the best. Thanks again.
ReplyDeleteYou are welcome! We can use ForEach loop and then inside we can always precedence constraints to make further decision what to do if file name is matching with our criteria.
ReplyDelete