SSIS package scheduling errors out

  • Hi,

    I am trying to set up job to run ssis package on 64 bit SQL 2005 EM. but it errrors out and gives me following error:

    Message

    Executed as user: user\user. ...ckage Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:47:03 PM Error: 2007-11-21 13:47:09.62 Code: 0xC0202009 Source: LoadActivity Connection manager "Sybase test DB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2007-11-21 13:47:09.62 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Sybase test DB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-11-21 13:47:. The step failed.

    I am running the package as proxy account and the credtial used by has the admin rights on the server!!

    OS is also 64 bit!

    Thanks

  • Hi, I got the exact same error today.

    The dtsx package works fine when executed from

    Execute package utility, but when I try to run it as a scheduled job

    I get the same error as you.

    The sql agent account is a AD account which sysadmin rights on the

    sql server. So that should not be the problem.

    I have tried recreating the step and it dosen't help.

    Someone what is the problem here ?

    I run 64 bit OS and SQL, could that be the problem ?

    Br,

    Staffan Olofsson

    Sirius International

  • have ame in one of my non prod environments.

    http://support.microsoft.com/kb/933835

    Error message when an SSIS package runs that is scheduled to run as a SQL Server Agent job: "An OLE DB error has occurred. Error code: 0x80004005"

    View products that this article applies to.

    Article ID:933835

    Last Review:November 20, 2007

    Revision:1.4

    SYMPTOMS

    When you schedule a Microsoft SQL Server 2005 Integration Services (SSIS) package to run as a SQL Server Agent job, you receive error messages that resemble the following when the job runs:

    OnError,SQL1V5,MyDomain\MyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".

    OnError,SQL1V5,MyDomain\MyAccount,DataSourceImport,{91A4C5D3-AC64-4DF8-B49E-7 3CFC9DAF979},{E4B9AD56-627E-4801-A262-C9EFA9F163AC},11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".

    OnError,SQL1V5,MyDomain\MyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "MyConnectionManager" failed with error code 0xC0202009.

    OnError,SQL1V5,MyDomain\MyAccount,DataSourceImport,{91A4C5D3-AC64-4DF8-B49E-7 3CFC9DAF979},{E4B9AD56-627E-4801-A262-C9EFA9F163AC},11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "MyConnectionManager" failed with error code 0xC0202009.

    OnError,SQL1V5,MyDomain\MyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1073450985,0x,component "MyConnectionManager" (72) failed validation and returned error code 0xC020801C.

    Back to the top

    CAUSE

    This issue occurs because the SQL Server 2005 Agent proxy account does not have permission for the Temp directory of the SQL Server Agent Service startup account.

    This issue occurs because the SQL Server 2005 Agent proxy account does not have permission for the Temp directory of the SQL Server Agent Service Startup account.

    Back to the top

    RESOLUTION

    To resolve this issue, you must change the permissions for the Temp directory of the SQL Server Agent Service startup account. Grant the Read permission and the Write permission to the SQL Server 2005 Agent proxy account for this directory.

    Back to the top

    STATUS

    This behavior is by design.

    Back to the top

    MORE INFORMATION

    The owner of the job determines the security context in which the job is run. If the job is owned by a SQL Server login account that is not a member of the Sysadmin fixed server role, the SSIS package runs under the context of the SQL Server Agent proxy account.

    If you want the SQL Server Agent proxy to run jobs that connect to an instance of SQL Server, the SQL Server Agent proxy account must have correct permissions to the instance of SQL Server.

    If you want the SQL Server Agent proxy account to run a job that runs as an SSIS package, the SQL Server Agent proxy account must have the Read permission and the Write permission to the Temp directory of the SQL Server Agent Service startup account.

  • I am getting this error today, OS is 32 bit and given the read/write permissions to the Temp directory, still getting the same error

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005.

    Any help or pointers will be really helpful.

    Thanks

    Zaheer


    Zaheer

  • There are two things you should do.

    To get rid of Class not registered issue, install the 32 bit version of SSIS on the server.

    To get rid of the other OLE issue, grant your proxy account read/write permission to following folder.

    C:\Documents and Settings\<SQL Agent Service Account>\Local Settings\Temp

    Note that there are two different types of accounts involved here. Agent Service account and the Proxy account.

    Refer to these pages for more information.

    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/59d2a4c5-7fc0-438d-a45e-89f420590f02

    http://msdn.microsoft.com/en-us/library/ms141766.aspx

    http://support.microsoft.com/kb/269074

    Good luck..!!!

  • a

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • pat-261672 (11/21/2007)


    Hi,

    I am trying to set up job to run ssis package on 64 bit SQL 2005 EM. but it errrors out and gives me following error:

    Message

    Executed as user: user\user. ...ckage Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:47:03 PM Error: 2007-11-21 13:47:09.62 Code: 0xC0202009 Source: LoadActivity Connection manager "Sybase test DB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2007-11-21 13:47:09.62 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Sybase test DB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-11-21 13:47:. The step failed.

    I am running the package as proxy account and the credtial used by has the admin rights on the server!!

    OS is also 64 bit!

    Thanks

    SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER means the package cannot connect to a data source, in this case "Source: Data Flow Task OLE DB Source [1]". My guess is you stored the data source credentials in BIDS while developing, deployed the package, tried to run it, and got the error. To resolve this issue setup a package configuration (XML file, for example) for the data source above. Under the data source properties in the package configuration wizard select "password" and store that info in the config file. Redeploy the package, reconfig SQL Agent and add the config file under the config tab and you should be good.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • I was getting this error because I foolishly copy/pasted The SSIS Server Conections (tab called Connection Managers) from Visual Studio 2008 to VS2005. It runs fine in debug mode but will not run on the server.

    I replaced the connections manually to fix it. Find and Replace in the XML might have worked but I didn't try it.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply