none
在SQLAgent中运行SSIS包报错。 RRS feed

  • 问题

  • SQLServer 2008 R2 EE CU7

    在BIDS中建立了一个包,然后导入到Integration Service,之后直接在Integration Service中运行,没有任何问题,于是建立Job来运行这个包,就报错了。

    错误信息为

    Executed as user: SZWRK2667\SYSTEM. tion.  End Error  Error: 2012-02-02 17:41:43.61     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:41:43 PM  Finished: 5:41:43 PM  Elapsed:  0.374 seconds.  The package execution failed.  The step failed.

     

    最开始SQLAgent服务的启动账号为Localsystem,于是我修改成./administrator,再执行那个Job,报错信息变为:

    Message

    Executed as user: SZWRK2667\Administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  5:44:57 PM  Error: 2012-02-02 17:44:58.18     Code: 0xC0047062     Source: Data Flow Task ADO NET Source [58]     Description: System.Data.SqlClient.SqlException: Login failed for user 'sa'.     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)     at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)     at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)     at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.SqlClient.SqlConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, 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, Object transaction)  End Error  Error: 2012-02-02 17:44:58.18     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component "ADO NET Source" (58) failed validation and returned error code 0x80131904.  End Error  Error: 2012-02-02 17:44:58.18     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-02-02 17:44:58.18     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:44:57 PM  Finished: 5:44:58 PM  Elapsed:  0.343 seconds.  The package execution failed.  The step failed

     

    谁知道应该怎么修复这个错误吗?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2012年2月2日 9:52

答案

  • 已经解决这个问题了。

    首先创建一个credential,之后创建一个proxy account,最后建立Job的时候将这个Job的Step “RUN AS”设置为这个proxy account。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2012年2月3日 2:15

全部回复

  • You got login failure for sa. What does the package do? Who's job owner? What's sql agent service account?
    2012年2月2日 14:17
  • You got login failure for sa. What does the package do? Who's job owner? What's sql agent service account?

    这个package是用来将数据从sqlserver迁移到mysql的.

    Job的Owner为Licalhost/administrator,SQL Agent的启动账号为LocalSYSTEM之后我又修改成了./administrator了


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2012年2月2日 14:24
  • Did you put in correct sa pwd in the package? Those accounts have proper MySql permission?

    2012年2月2日 16:54
  • Did you put in correct sa pwd in the package? Those accounts have proper MySql permission?

    我在package中设置connection的时候 就已经写明了sa和mysql中的root密码了。确定是没问题的,

    而且我直接在integration service中执行包也是没问题的


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2012年2月3日 0:42
  • 已经解决这个问题了。

    首先创建一个credential,之后创建一个proxy account,最后建立Job的时候将这个Job的Step “RUN AS”设置为这个proxy account。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2012年2月3日 2:15