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

Posted on 2008-11-17
Last Modified: 2016-02-13
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
Question by:coventri
    LVL 22

    Expert Comment

    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.
    LVL 17

    Expert Comment

    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.

    LVL 22

    Accepted Solution

     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.
    LVL 1

    Author Comment

    Hi  HoggZilla/PedroCGD,
    Ur code helped me a lot .Its resolved my problem

    Thanks and Regards,

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now