我创建了一个SSIS包,它将该包从Oracle 10g服务器导出到SQL server 2008中。当我通过Microsoft BIDS运行该程序包时,它运行良好,当我创建它的程序包并在Integration Services中运行它时。只有当我通过作业调度程序(SQL Server代理)进行调度时,它才会失败
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 HHSHuser7199. 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: HHSHDSSSERVER$. 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 HHSHuser7199. 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: HHSHDSSSERVER$.
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: HHSHDSSSERVER$. 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
大多数在BIDS中运行良好但在SQL作业中运行不好的问题都是由以下情况引起的:
-
数据源连接或文件访问问题(当运行sql代理的用户没有对数据库或目标文件的正确权限时)。
-
包保护级别(pwd是敏感数据,有时不会根据保护级别进行复制)。
-
64位问题(由于64位系统没有Jet驱动程序,在64位操作系统上运行时,您需要在sql作业中使用32位DTEXEC,或者设置"使用32位运行时"选项,当您创建SSIS作业步骤时,该选项位于"执行选项"选项卡上,请查看下面链接上的图像以查看它)。
32位运行时选项图像
关于这个主题的信息,我建议你看一下下面的文章,它有关于这类情况的详细信息。。
如何解决SQL代理作业中SSIS包执行失败的问题?
希望这对你有帮助。。
亲切问候,
SQL Server代理有一个小故障,我也有类似的问题,所以我只需重新构建项目,如果你有任何脚本,我会重新构建它,然后再次构建项目,删除包引用的路径,用位于bin文件夹中的包重新创建路径,然后计划运行作业,有时SQL Server代理无法正确缓存请求。我知道这个帖子是两年前被问到的,但这是给目前有问题的人的。而且,如果你在包中有发送电子邮件任务,如果你不在通讯组列表中,你也在发送电子邮件,那么当你将包安排为作业时,包就不会运行。