none
ETL fails when run as a scheduled task - Run fine when executed manually

    Question

  • Hi Whe I run my job as a scheduled task I get the following errors when running our ETL packages. But runs fine when kicked off manually. Please can somebody advise me on this. It is running on a Windows 2008 OS with SQL Server 2008 r2.

      1. The component was unable to process the data. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. 
      2. SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure. 

    IT network ADMIN

    Thursday, December 19, 2013 11:07 AM

Answers

  • Hey,

    Can you try the following?

    Open the following 32-bit scheduler: C:\Windows\SysWOW64\taskschd.msc. Create a Basic Task. In the program script use C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec  and in the Add arguments use /f M:\MCT_SSIS\MCT_SSIS\MyPackage.dtsx" /DECRYPT password. For the Start in use C:\Windows\SysWOW64\ 

    Let me know if it works.

    Regards,

    Razvan


    Per aspera ad astra!
    journeyintobi.com



    Thursday, December 19, 2013 3:08 PM

All replies

  • I am getting the following errors when running our ETL packages.  Please can somebody advise me on this. It is running on a Windows 2008 OS with SQL Server 2008 r2.

    1. The component was unable to process the data. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. 
    2. SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure. 

    Thanks


    Tuesday, December 17, 2013 12:47 PM
  • This appears to be an SSIS package. Please post your question in the SQL Server Integation Services forum.

    However, this kind of error is most likely a bug in SSIS.  I would suggest installing the latest service pack and cumulative update and see if the problem is resolved.

    Please see:

    http://support.microsoft.com/kb/321185

    Tuesday, December 17, 2013 2:05 PM
  • Hello Hailey,

    have you had a point in time where those ETL packages worked without any problem? Did you install any updates on the machine lately?

    Some sources point to .NET updates for example.

    Best regards,

    Razvan


    Per aspera ad astra!
    journeyintobi.com


    Tuesday, December 17, 2013 2:25 PM
  • Are you running out of memory?
    Tuesday, December 17, 2013 2:29 PM
  • Hi Tom

    The SSIS is currently patched to 2008 R2 SP2.  The Live and Dev servers are both running the same ETL with same version of SQL Server, Its runs fine on the Live environment but fails everyday on Dev environment. Normally it takes a lot longer and then finally errors out at different stages on different days.

    Regards


    IT network ADMIN

    Tuesday, December 17, 2013 3:03 PM
  • Hi Nigel

    The SSIS is currently patched to 2008 R2 SP2.  The Live and Dev servers are both running the same ETL with same version of SQL Server, Its runs fine on the Live environment but fails everyday on Dev environment. Normally it takes a lot longer and then finally errors out at different stages on different days.

    As it progresses it starts t consume more memory and CPU and finally errors out or is stopped by myself as the environment become very slow. I have tried allocating more memory to the SQL Server (same as live) but the behaviour has not changed.

    Regards


    IT network ADMIN

    Tuesday, December 17, 2013 3:08 PM
  • Hi Razvan

    No it errors out daily.

    Regards


    IT network ADMIN

    Tuesday, December 17, 2013 3:08 PM
  • Thanks for the reply, Hailey. Can you tell me if the servers are identical from the windows patches point of view? .NET, Windows SP and so on? 

    Are you using the same data source? Is it identical?

    And also I would chose to enable logging in the SSIS package to see exactly where the error is being thrown.


    Per aspera ad astra!
    journeyintobi.com



    Tuesday, December 17, 2013 3:16 PM
  • Yes both server are running the same version of OS (Win Server 2008 R2 Stn Version 6.1 Build 7601: SP1). They both have .net framework v3.5.1 installed as a added feature. Only that the Dev server has the 'WCF Activation; installed as well as added feature of .Net

    They are both using the same datasource.

     


    IT network ADMIN

    Tuesday, December 17, 2013 3:32 PM
  • This:

    "The component was unable to process the data. Attempted to read or write protected memory. This is often an indication that other memory is corrupt.  "

    Is a serious error, and indicates a bug somewhere is corrupting memory.  In SSIS this is often related to 3rd party ODBC or OleDB drivers, which are unmanaged code that run in-process.

    What data sources and drivers are you using?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, December 17, 2013 3:38 PM
  • Can you try to remove the WCF Activation and see if you get the same error? And also check this link.

    Per aspera ad astra!
    journeyintobi.com


    Tuesday, December 17, 2013 3:43 PM
  • I am using the following drivers

    'Protechnic PHC Driver 32 bit' and ' Unify SQLBase 11.6'


    IT network ADMIN

    Tuesday, December 17, 2013 4:02 PM
  • Well, one of them is failing.  To figure out which one, try removing one or the other and replacing it with a flat file or raw file source or destination.

    Once you narrow it down, you'll need to address it with the driver vendor.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, December 17, 2013 4:11 PM
  • Hi Razvan

    Made the required changes to the .Net 3.5.1

    It ran fine during the day and has failed when  a schedule task ran it after midnight. Same error.

    So it seems that it runs fine at one instance and fails as scheduled.

    Any ideas

    Regards


    IT network ADMIN

    Thursday, December 19, 2013 9:46 AM
  • Hey Hailey,

    As I understand, when you run the package alone, it is working without any issues, but when you schedule it, you get the respective error. What user are you using for the scheduled task? Does it have the same rights as the one you are using to run the package alone? If not, can you grant the same rights to the user defined for the SQL Agent?

    Razvan


    Per aspera ad astra!
    journeyintobi.com


    Thursday, December 19, 2013 10:07 AM
  • Hello Hailey,

    I see you opened a new thread for this issue :). I understand that the .NET framework WCF activation solved your trick and that the package can run correctly in manual mode. Is it correct?

    For the scheduling part, as stated in the previous thread, can you check the following:

    - the user which runs the scheduled task has the same rights as the user you are manually running your package?

    - Have you tried forcing your package to run in 32 bit mode when using SQL Server Agent? (I presume you are using this method)

    - The scheduled task is the same as in production? As I remember, there works perfectly.

    Thank you,

    Razvan


    Per aspera ad astra!
    journeyintobi.com

    Thursday, December 19, 2013 11:27 AM
  • Hi

    The user running the schedule task is the same user I user to run it manullay.

    The schedule task is same as prod

    Pardon my ignorance on the this point. Why does the SSIS package need to be run as 32bit and how can I find this out.

    Regards


    IT network ADMIN

    Thursday, December 19, 2013 11:52 AM
  • Hi there. 

    I am asking you to try to run it on 32 bit because the ODBC drivers might have a problem on 64bit bit. You can change the way the package is run by going into the SQL Server Job, open the SSIS step, go under the Execution options tab. Check the 32-bit runtime option.

    See if this solves your issue :).


    Razvan


    Per aspera ad astra!
    journeyintobi.com






    Thursday, December 19, 2013 11:57 AM
  • Hi

    You can run the job as 32 bit and follow the below steps.

    1. Open the job properties

    2. Go to steps tab

    3. Double click the step which you want to run as 32 bit.

    4. Go to execution options tab and you can see a check box for "Use 32 bit runtime"

    5. Check this box and click OK, and again another OK.

    Now this job step will use 32 bit runtime.

    Cheers

    Mani@YourSQLMan.com


    Dr.Subramani Paramasivam

    Thursday, December 19, 2013 11:58 AM
  • Hi

    Checked. Dont think this is running as a sql server agent job. it run off a batch file.

    Can confirm that it uses 32 bit drivers and runs dtexec from C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec. Same as live environment. So it is running as 32 bit.

    Thanks for your patience on this.

    Still wondering as to why does the schedule job fails.


    IT network ADMIN

    Thursday, December 19, 2013 12:41 PM
  • A simple way to see if you really have issues on 64bit without the use of the SQL Server Agent is running the following commands:

    "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx

    The first one runs without an issue as it is 32 bit. What about the second one? Which is 64 bit. 

    Also, can you give a bit more details on how you run that scheduled job? What are you using exactly and how. If your schedule is running in 64 bit mode, then there would be your problem.

    Most welcome!

    Razvan


    Per aspera ad astra!
    journeyintobi.com




    Thursday, December 19, 2013 12:55 PM
  • Hi

    The Schdule task actions a batch file with the following command

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /f M:\MCT_SSIS\MCT_SSIS\MyPackage.dtsx" /DECRYPT password

    The default for dtexec is 64 bit mode and we only have 32 bit drivers, that explains the url above.

    Thanks


    IT network ADMIN

    Thursday, December 19, 2013 2:58 PM
  • Hey,

    Can you try the following?

    Open the following 32-bit scheduler: C:\Windows\SysWOW64\taskschd.msc. Create a Basic Task. In the program script use C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec  and in the Add arguments use /f M:\MCT_SSIS\MCT_SSIS\MyPackage.dtsx" /DECRYPT password. For the Start in use C:\Windows\SysWOW64\ 

    Let me know if it works.

    Regards,

    Razvan


    Per aspera ad astra!
    journeyintobi.com



    Thursday, December 19, 2013 3:08 PM
  • Hi Hailey1980,

    Does your SQL Server Agent Job run a SSIS Package Job step or an Operating System (CmdExec) Job step? Does the SQL Server Agent job runs under the SQL Server Agent Service Account or a proxy account? Are the error messages you posted gotten from the job history?

    Generally, the issue that a SSIS package runs successfully in BIDS but fails to execute in a SQL Server Agent job are caused by one of the following factors:

    • Package Protection Level issue
    • Data Source Connection Issue
    • File or Registry Key Access Issue
    • 64-Bit Driver Issue

    If you use CmdExec job step, make sure the Service Account or proxy account under which the job runs has sufficient permission on the directory “M:\MCT_SSIS\MCT_SSIS”. For the other possible reasons, I suggest that you walk through the following thread to troubleshoot this issue:
    http://social.technet.microsoft.com/Forums/sqlserver/en-US/e13c137c-1535-4475-8c2f-c7e6e7d125fc/how-do-i-troubleshoot-ssis-packages-failed-execution-in-a-sql-agent-job?forum=sqlintegrationservices

    Regards,


    Mike Yin
    TechNet Community Support

    Friday, December 20, 2013 8:57 AM
    Moderator
  • Hi Razvan:

    Thanks for all your help on this. I created a new task, as advised, and has been running fine for the last four days, without error.

    I have marked your advise as an answer now.

    Thanks again


    IT network ADMIN

    Monday, December 23, 2013 1:48 PM
  • Hello Hailey,

    I am glad it got sorted out!

    Best regards,

    Razvan


    Per aspera ad astra!
    journeyintobi.com

    Monday, December 23, 2013 3:09 PM