none
SSIS Package runs perfectly, but when ran as a job fails miserably. RRS feed

  • Question

  • I have a script in place that extracts emails and moves some files around, opening, saving, closing.  It works perfectly when run through SSIS but as a SQL Server job it doesn't create these text files it's supposed to.  Infact the only thing it does is move the email in to the folder it's supposed to be in and then fails out.  
    Saturday, April 13, 2013 10:25 PM

Answers

  • 1)Perhaps you could try to create a Credentials and Proxy with your own account and then check whether the jobstep runs successfully.

    2) Add a try catch to your Script Task and in the catch add a FireError to log the exception (and use logging within SSIS). Something like:

    try
    {
    	// Your code
    	
    	// end of your code
    	Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {
    	// Raise event so it can be logged by SSIS
    	Dts.Events.FireError(0, "Your Script Task name", "An error occurred: " + e.Message.ToString(), "", 0);
    	Dts.TaskResult = (int)ScriptResults.Failure;
    }
    


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Eileen Zhao Monday, April 22, 2013 6:22 AM
    Sunday, April 14, 2013 7:54 PM
    Moderator
  • Please put a breakppoint as suggested by all and let us know your error.

    Most probably the account the account which is used as proxy by SSIS job, is not having access to the directories.


    Abhinav
    http://bishtabhinav.wordpress.com/

    • Marked as answer by Eileen Zhao Monday, April 22, 2013 6:22 AM
    Monday, April 15, 2013 4:17 PM
    Moderator

All replies

  • What is the error if any, job gives, while running. What are you doing exactly explain the moving the files around post extraction, I am assuming you are using a script task to extract files from Email, post that what do you do? 

    What is the environment in terms of bitness? 


    Abhinav
    http://bishtabhinav.wordpress.com/

    Saturday, April 13, 2013 10:33 PM
    Moderator
  • Hi,

    I remember having a similar issue where my SSIS job ran perfectly when I ran it manually via BIDS or SSMS, but when it ran as a scheduled job it failed. The answer for me was to ensure that the service account responsible for running the SSIS package had sufficient access rights.

    Also please post the error log which the SQL Server agent generated when your SSIS package job failed to run.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood


    Saturday, April 13, 2013 10:52 PM
  • Two common problems when something works in BIDS/SSDT but not as a job:

    1) authorization (the user that runs the jobstep isn't the same user as when you run the package manually)

    2) BIDS/SSDT runs in 32 bit mode, the jobstep in 64bit. (things like old drivers or offices doesn't work in 64bit)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Sunday, April 14, 2013 8:58 AM
    Moderator
  • The exact error I'm getting is 

    Started:  8:37:08 AM
    Error: 2013-04-14 08:38:57.89
       Code: 0x00000008
       Source: Set Up Import Directory - email 
       Description: The script returned a failure result.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).

    Now the weird thing is it works in SSIS both on my login and the administrator user that run the sql server as a job, but as a job it still fails.  Now I know the administrator account does have some 64bit drivers but could that be it even though that is not the specific error being returned?  Thanks for your assistance on this guys, it has been rattling my head for the past week.  I have thought it to be a permission issue, but I don't know enough of where/what to check for and everytime I bring the fact up to my bosses it is a dismissed idea that doesn't get looked in to.

    Sunday, April 14, 2013 3:51 PM
  • A bit of an update, I have 7 tasks, every task can succeed except for the script task.  Is there a way I can put a break point to see at what point the script will fail?
    Sunday, April 14, 2013 5:51 PM
  • Yes you can set a break point in a script task within your control flow; - http://msdn.microsoft.com/en-us/library/ms135952.aspx

    Does the account you use to run the SSIS package within a SQL agent job have access to the directory this SSIS package is trying to import the e-mails from?

    I hope this helps. This must be very frustrating for you.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood

    Sunday, April 14, 2013 6:19 PM
  • 1)Perhaps you could try to create a Credentials and Proxy with your own account and then check whether the jobstep runs successfully.

    2) Add a try catch to your Script Task and in the catch add a FireError to log the exception (and use logging within SSIS). Something like:

    try
    {
    	// Your code
    	
    	// end of your code
    	Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {
    	// Raise event so it can be logged by SSIS
    	Dts.Events.FireError(0, "Your Script Task name", "An error occurred: " + e.Message.ToString(), "", 0);
    	Dts.TaskResult = (int)ScriptResults.Failure;
    }
    


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Eileen Zhao Monday, April 22, 2013 6:22 AM
    Sunday, April 14, 2013 7:54 PM
    Moderator
  • Please put a breakppoint as suggested by all and let us know your error.

    Most probably the account the account which is used as proxy by SSIS job, is not having access to the directories.


    Abhinav
    http://bishtabhinav.wordpress.com/

    • Marked as answer by Eileen Zhao Monday, April 22, 2013 6:22 AM
    Monday, April 15, 2013 4:17 PM
    Moderator