Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSIS 2008 Force Failure on Package

Posted on 2010-09-20
9
Medium Priority
?
1,378 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

581 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