SSIS Job scheduler failing, but running fine in BIDS and in MSDB integration Services

2
votes

I have created an SSIS package , that exports that from Oracle 10g server into SQL SERVER 2008. The package runs fine , when I run it through Microsoft BIDS, It run fine , when I create a package of it and runs it in Integration Services. It gets failed only when I schedule it through Jobs scheduler (SQL Server Agent)

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
07/26/2012 15:20:12,PATH JOB,Error,0,DSSSERVER,PATH JOB,(Job outcome),,The job failed.        
 The Job was invoked by User HHSH\user7199.  The last step to run was step 1 (PATH JOB).,00:00:01,0,0,,,,0
07/26/2012 15:20:12,PATH JOB,Error,1,DSSSERVER,PATH JOB,PATH JOB,,Executed as user: HHSH\DSSSERVER$. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  3:20:12 PM  Error: 2012-07-26 15:20:12.93     Code: 0xC0047062     Source: CLINICS ADO NET Source [1]     
Description: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.     
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName<c/> String connStr<c/> Object transaction)     
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper<c/> Object transaction)  End Error  Error: 2012-07-26 15:20:12.93     
Code: 0xC0047017     Source: CLINICS SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131500.  End Error  Error: 2012-07-26 15:20:12.93     
Code: 0xC004700C     Source: CLINICS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-07-26 15:20:12.93     Code: 0xC0024107     Source: CLINICS      
Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:20:12 PM  Finished: 3:20:12 PM  Elapsed:  0.624 seconds.  The package execution failed.  
The step failed.,00:00:01,0,0,,,,0
07/26/2012 15:18:21,PATH JOB,Error,0,DSSSERVER,PATH JOB,(Job outcome),,The job failed.  The Job was invoked by User HHSH\user7199.  The last step to run was step 1 (PATH JOB).,00:00:01,0,0,,,,0
07/26/2012 15:18:22,PATH JOB,Error,1,DSSSERVER,PATH JOB,PATH JOB,,Executed as user: HHSH\DSSSERVER$. 
Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  3:18:22 PM   
Error: 2012-07-26 15:18:22.76     Code: 0xC0047062     Source: CLINICS ADO NET Source [1]     Description: 
Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.     
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName<c/> String connStr<c/> Object transaction)     
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper<c/> Object transaction)  End Error  Error: 2012-07-26 15:18:22.76     
Code: 0xC0047017     Source: CLINICS SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131500.  End Error  Error: 2012-07-26 15:18:22.76     
Code: 0xC004700C     Source: CLINICS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-07-26 15:18:22.76     Code: 0xC0024107     
Source: CLINICS      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:18:22 PM  Finished: 3:18:22 PM  Elapsed:  0.655 seconds.  The package execution failed.  The step failed.,00:00:00,0,0,,,,0
07/24/2012 13:48:00,PATH JOB,Error,0,DSSSERVER,PATH JOB,(Job outcome),,The job failed.  
The Job was invoked by Schedule 23 (JOb Schedule for LAb PAthology).  The last step to run was step 1 (PATH JOB).,00:00:00,0,0,,,,0
07/24/2012 13:48:00,PATH JOB,Error,1,DSSSERVER,PATH JOB,PATH JOB,,Executed as user: HHSH\DSSSERVER$. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  1:48:00 PM  Error: 2012-07-24 13:48:00.72     Code: 0xC0047062     Source: CLINICS ADO NET Source [1]     Description: 
Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.     
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName<c/> String connStr<c/> Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper<c/> Object transaction)  End Error  Error: 2012-07-24 13:48:00.72     Code: 0xC0047017     Source: CLINICS SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131500.  End 
 Error  Error: 2012-07-24 13:48:00.72     Code: 0xC004700C     Source: CLINICS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-07-24 13:48:00.72     Code: 0xC0024107     Source: CLINICS      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:48:00 PM  Finished: 1:48:00 PM  Elapsed:  0.639 seconds.  The package execution failed.  The step failed.,00:00:00,0,0,,,,0
2
sql-serversql-server-2008ssisbids
If it runs from when you run it in BIDS and when you run it through the IS thing but fails from the Agent, that would make me think there is something amiss with the agent. Have you verified that the service account that runs the package is properly credentialed to communicate with Oracle? Does it have file system access to any Oracle drivers or whatnot? – billinkc
Well I m using Windows Authentication for the same. I m confused how can we check whether the file system access to any Oracle drivers or whatnot? – user1141584

2 Answers

11
votes

Most of the problems of running well in BIDS but not on SQL job arise due to these situations:

  • Data source connection or File access issue (when the user running the sql agent doesn't have the right permissions to the db or to the destination file).

  • Package protection level (pwd are sensitive data, and sometimes are not copied depending on the protection level).

  • 64bit issue (Since there's no Jet Driver for 64bit systems, when running on a 64bit OS you need to either use the 32bit DTEXEC in your sql job or set the "Use 32bit runtime" option, it's on the "Execution options" tab when you're creating a SSIS job step, check the image on link below for seeing it).

32Bit runtime option image

For information on this topic, I recommend you have a look at the following article, it has detailed information on these kind of situations..

How do I troubleshoot SSIS packages failed execution in a SQL Agent job?

Hope this helps you..

Kind regards,

0
votes

The SQL Server Agent has a glitch in it, i had a similar problem, so what i did was simply built the project again, if you have any script take i built that again and then built the project again, removed the path that the package was referencing, recreated the path with the package located in the bin folder and then scheduled the job to run and it did, sometime SQL Server Agent does not cache requests correctly. I know this post was asked two years ago, but this is for someone having issues currently. And also packages dont run when you schedule it as a job, if you have sendemail tasks in the package and if your not part of the distribution list your sending the emails too.