If the file isnt there on ftp server generate error by using SSIS

SSIS package has to load the data from flat file on ftp server.The package is scheduled  to execute Quarterly using sql  Server agent .
If the file isnt there on ftp server how can i Fail the job .Keep retrying until file arrives
LVL 1
coventriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
Dear Friend,
I made a simple SSIS package for you.
Create a new SSIS Project, add the attached package (rename it to dtsx).
Update the flatfile connection and join an see results.
Inside script you can use warning or error events.
Try to delete or rename the text file to test the package.
Helped?
regards
Pedro

www.pedrocgd.blogspot.com
Package-EE49-dtsx.txt
BudgetEx.txt
FileFound.JPG
0
Steve HoggITCommented:
Hi coventri,
Your goal, if the file is not there, is to fail the SSA job. To accomplish this in SSIS you can simply create a Script Task that has a result of Failure. See below.
You can evaluate conditions in your Script Task to determine if you want to fail or continue. Like checking the value of a variable.
Make sure your SSIS package property, MaximumErrorCount is set to 1. In the Control Flow window, click in the open space and then open the properties tab.

script-task-failure.bmp
0
PedroCGDCommented:
COventri,
 the option Failure is one property of DTS Result.
Check the last line of code... where you can set to sucess as I made in the script, or set as a failure.
As I told you, you have several alternatives, let me know if you have some doubts.
Regards,
Pedro
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coventriAuthor Commented:
Hi  HoggZilla/PedroCGD,
Ur code helped me a lot .Its resolved my problem

Thanks and Regards,
coventri
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.