Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS 2008 Force Failure on Package

Posted on 2010-09-20
9
Medium Priority
?
1,376 Views
Last Modified: 2012-05-10
Hi,

I have an SSIS package invoked by the OS Task Scheduler, (on Windows Server 2008 32 bit), that will connect to a remote FTP server and download files.

The challenge is that the server we are connecting to is proving unreliable, connection keeps timing out. I have this exception caught within the package however I would like to fail the package, (so that a fail event appears within the Task Scheduler log), so that i can exploit the 'Execute task again in the event of failure' option within the settings of the scheduled task.

I have done the following:

* Created a script task within the event handler which raises a dts.events.FireError and the default task result will always be ScriptResults.Failure.

* Updated the properties of the script task, (not all at once but in various combinations):
  - FailPackageOnFailure - changed to True
  - FailParentOnFailure - changed to True
  - ForcedExecutionValue - changed to '-1000' - have also tried -1, 1 also.
  - ForcedExecutionValue - changed to true

* Updated the properties of the OnError Event Handler (package level):
  - FailPackageOnFailure - changed to True
  - FailParentOnFailure - changed to True

Doesn't seem to matter what I do I cannot get an error to pass through to the Schedule Tasks app.

Any ideas anyone?

0
Comment
Question by:Peachy_Steve
  • 6
  • 3
9 Comments
 
LVL 4

Accepted Solution

by:
timexist earned 2000 total points
ID: 33722301
I think this problem cannot be solved by SSIS only.

maybe you should do something else.

I have a windows application, it downloads email and  dettachs email to a folder and then trigger a ssis package to load the attachments.

So maybe you should change the logic of doing the things.

1, you can create a table to store processed files records,
before you start try the ftp task, you check database,
if last task is failed or this is the first time today, then go to ftp server.

2, you can schedule the task for half day run multiple time.(per 30 minutes).

0
 
LVL 1

Author Comment

by:Peachy_Steve
ID: 33722309
Thanks for your response.

These are good suggestions, I will consider them further.

btw, what application are you using to download email attachments just out of interest?
0
 
LVL 4

Expert Comment

by:timexist
ID: 33722316
I  can pass you the code. hold on a minute
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:timexist
ID: 33722332
I think these are vs 2008 c#.net code
for dettach emails
all the parameters are in app.config.
so you can setup your own values.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Outlook= Microsoft.Office.Interop.Outlook;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Runtime.InteropServices;

namespace MTC_DetachEmail.cls
{
    class clsOutLook
    {
        /// <summary>
        /// save attachment from email
        /// </summary>
        /// 
        public static bool saveAttachment()
        {
            string folderName = System.Configuration.ConfigurationManager.AppSettings["folderName"];
            string folderNameHC = System.Configuration.ConfigurationManager.AppSettings["folderNameHC"];
            string folderNameOt = System.Configuration.ConfigurationManager.AppSettings["folderNameOt"];
            string test = System.Configuration.ConfigurationManager.AppSettings["serverUserName"];
            string connstring = System.Configuration.ConfigurationManager.AppSettings["connstring"];
            string sprocName = System.Configuration.ConfigurationManager.AppSettings["sprocName"];
            string profilePWD = System.Configuration.ConfigurationManager.AppSettings["profilePWD"];
            string profileName = System.Configuration.ConfigurationManager.AppSettings["profileName"];
            string fileFormatTkt = System.Configuration.ConfigurationManager.AppSettings["fileFormatTkt"];
            string fileFormatHC = System.Configuration.ConfigurationManager.AppSettings["fileFormatHC"];
            bool saveFileInFolder = false;
            string folderTmp = string.Empty;
            string fileTmp = string.Empty;
            int AttachCnt = 0;
            try
            {

                Outlook.Application oApp = new Outlook.Application();
                Outlook.NameSpace oNS = oApp.GetNamespace("mapi");
                oNS.Logon(profileName, profilePWD, false, true);
                Outlook.MAPIFolder oInbox = oNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);

                //Get the Items collection in the Inbox folder.
                Outlook.Items oItems = oInbox.Items;
                oItems = oItems.Restrict("[Unread] = true");
                // Get the first message.
                // Because the Items folder may contain different item types,
                // use explicit typecasting with the assignment.
                Outlook.MailItem oMsg;
                int itemCount = 0;
                itemCount = oItems.Count;
                Console.WriteLine("Total unread mail is {0}.", itemCount);
                while (itemCount > 0)
                {// Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Outlook.MailItem'. 
                    if (oItems[itemCount] is Outlook.MailItem)                     
                    {
                        oMsg = (Outlook.MailItem)oItems[itemCount];
                        AttachCnt = oMsg.Attachments.Count;
                        Console.WriteLine("total attachment is {0}.", AttachCnt);
                        Console.WriteLine("current mail is No.{0}. ", itemCount);
                        if (oMsg.Attachments.Count > 0)
                        {
                            for (int i = 1; i <= AttachCnt; i++)
                            {
                                fileTmp = oMsg.Attachments[i].FileName.ToUpper();
                                if (fileTmp.Substring(0, 2) == fileFormatTkt)
                                {
                                    folderTmp = folderName;
                                }
                                else if (fileTmp.Substring(0, 2) == fileFormatHC)
                                {
                                    folderTmp = folderNameHC;
                                }
                                else
                                {
                                    folderTmp = folderNameOt;
                                }
                                Console.WriteLine("File saving folder is {0}.", folderTmp);
                                Console.WriteLine("Attachment file name is {0}.", oMsg.Attachments[i].FileName);
                                oMsg.Attachments[i].SaveAsFile(folderTmp + @"\" + oMsg.Attachments[i].FileName);
                                saveFileInFolder = true;
                                oMsg.UnRead = false;
                            }
                        }
                    }
                    itemCount--;
                }
                oNS.Logoff();
                oMsg = null;
                oItems = null;
                oInbox = null;
                oNS = null;
                ((Outlook._Application)oApp).Quit();
                Marshal.FinalReleaseComObject(oApp);
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return saveFileInFolder;
        }
    }
}

Open in new window

0
 
LVL 4

Expert Comment

by:timexist
ID: 33722335
the code works in exchange 2003 and also exchange 2010
0
 
LVL 4

Expert Comment

by:timexist
ID: 33722354
this part is main program, it will trigger the ssis package if there is attachment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Configuration;
using System.Threading;

namespace MTC_DetachEmail
{
    class Program
    {
        static void Main(string[] args)
        {
            string packageFullName = System.Configuration.ConfigurationManager.AppSettings["packageFullName"];
            string scheduleTime = System.Configuration.ConfigurationManager.AppSettings["scheduleTime"];
            int scheduleTimeInt = Convert.ToInt32(scheduleTime);
            //int mailResult = 0;
            int scheduleTimeTmp = scheduleTimeInt;
            try
            {
                if (MTC_DetachEmail.cls.clsOutLook.saveAttachment())
                {
                    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                    Package package = app.LoadPackage(packageFullName, null);
                    DTSExecResult result = package.Execute();
                    //mailResult = Convert.ToInt32(result.ToString());
                    //if (mailResult > 0)
                    //{
                    //    scheduleTimeTmp = 60;
                    //}
                    Console.WriteLine("Package resultss : {0}", result.ToString());
                }
                else
                {
                    Console.WriteLine("Package resultss : nothing");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Thread.Sleep(3000);
           // Console.ReadLine();// (ex.ToString());

        }
    }
}

Open in new window

0
 
LVL 4

Expert Comment

by:timexist
ID: 33722364
that is all. very simple. only two keys.
1, outlook component.
2, component to run ssis package.

have fun.
0
 
LVL 1

Author Comment

by:Peachy_Steve
ID: 33722368
Thanks timexist. I know it's not related to this post however I think I can make use of this for another application.
0
 
LVL 1

Author Comment

by:Peachy_Steve
ID: 33730392
After further thought I believe the reason why a Windows event cannot be generated on package failure is because an SSIS package context will execute within the context of a run time environment; as long as the runtime environment completes successfully then a Windows failure event will not created even if the pcakage fails.

Therefore I accept your solution timexist that the design approach should be changed.

Thanks for your help.
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

886 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