Solved

SSIS 2008 Force Failure on Package

Posted on 2010-09-20
9
1,330 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
 
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
Are your corporate email signatures appalling?

Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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