[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-17
4
Medium Priority
?
291 Views
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
0
Comment
Question by:coventri
  • 2
4 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22983031
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22983945
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
 
LVL 22

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 22984061
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
 
LVL 1

Author Comment

by:coventri
ID: 23015165
Hi  HoggZilla/PedroCGD,
Ur code helped me a lot .Its resolved my problem

Thanks and Regards,
coventri
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 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