SSIS Job scheduler failing, but running fine in BIDS and in MSDB integration Services
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 Attempted07/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,,,,007/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,,,,007/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,,,,007/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,,,,007/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,,,,007/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
Any input on this would be appreciated.
Thanks !!!
July 26th, 2012 3:42pm
are you using windows authentication to run the package from BIDS? when you run a sql job, you can choose under what account it should run, normally under the user who is used to run sql engine.
http://scn.sap.com/thread/2014441 this link may help you.
regards
joon
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 4:03pm
Yes , I m using Windows Authetication to run the package from BIDS as well as Intgeration Services. I m not confused , why this doesnt work through Job. Well, I dont have a SQL Authentication account to run this in SQL Server Job Agent. Hence , this doesnt
work for me.
July 26th, 2012 4:18pm
are you trying to run it through a SQL Server Job?
if yes, you need to create a proxy account for the user account that has privilege to underlying databases and run SQL Server job with that proxy account. here you can see full solution for that:
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/69441/http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2012 10:52pm
are you trying to run it through a SQL Server Job?
if yes, you need to create a proxy account for the user account that has privilege to underlying databases and run SQL Server job with that proxy account. here you can see full solution for that:
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/69441/http://www.rad.pasfu.com
July 26th, 2012 10:52pm