SSIS 作业调度程序失败，但在 BIDS 和 MSDB 集成服务中运行良好 [英] SSIS Job scheduler failing, but running fine in BIDS and in MSDB integration Services
问题描述
我创建了一个 SSIS 包，该包将其从 Oracle 10g 服务器导出到 SQL SERVER 2008.该包运行良好，当我通过 Microsoft BIDS 运行它时，它运行良好，当我创建它的包并在其中运行时集成服务.只有当我通过作业调度程序(SQL Server 代理)进行调度时，它才会失败
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
推荐答案
在 BIDS 中运行良好但在 SQL 作业上运行不好的大多数问题是由于以下情况产生的:
Most of the problems of running well in BIDS but not on SQL job arise due to these situations:
数据源连接或文件访问问题(当运行 sql 代理的用户没有对数据库或目标文件的正确权限时).
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).
包保护级别(pwd 是敏感数据，有时不复制取决于保护级别).
Package protection level (pwd are sensitive data, and sometimes are not copied depending on the protection level).
64 位问题(由于 64 位系统没有 Jet 驱动程序，因此在 64 位操作系统上运行时，您需要在 sql 作业中使用 32 位 DTEXEC 或设置使用 32 位运行时"选项，它位于执行选项"选项卡，当您创建 SSIS 作业步骤时，请查看下面链接中的图像以查看它).
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).
有关此主题的信息，我建议您查看以下文章，其中包含有关此类情况的详细信息..
For information on this topic, I recommend you have a look at the following article, it has detailed information on these kind of situations..
希望对你有帮助..
亲切的问候，
这篇关于SSIS 作业调度程序失败，但在 BIDS 和 MSDB 集成服务中运行良好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
