Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SSIS 2008 Force Failure on Package

Posted on 2010-09-20
9
1,348 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

839 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