Solved

SSIS 2008 Force Failure on Package

Posted on 2010-09-20
9
1,351 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 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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