Error running SSIS package

  • MattF

    SSCrazy

    Points: 2285

    Hi,

    We get an error running a SSIS package which calls another package. Both packages execute Ok independently. Error: "The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getpackage', database 'msdb', schema 'dbo'". The user running the package has db_dtsOperator role in the MSDB (it is also sysadmin). This is occurring on more than 1 test server (Win Server 2003 SP2, SQL 2005 SP 2, but the same package is working Ok on our production box.

    Thanks

    MattF

  • Greg Charles

    SSC-Forever

    Points: 45403

    Is the ProtectionLevel of the called package set to "ServerStorage"?

    Greg

  • VALEK

    SSCarpal Tunnel

    Points: 4206

    MattF (3/31/2008)


    Hi,

    We get an error running a SSIS package which calls another package. Both packages execute Ok independently. Error: "The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getpackage', database 'msdb', schema 'dbo'". The user running the package has db_dtsOperator role in the MSDB (it is also sysadmin). This is occurring on more than 1 test server (Win Server 2003 SP2, SQL 2005 SP 2, but the same package is working Ok on our production box.

    Thanks

    looks like your user has no access to msdb database.

    Or the guest account.

    Check the permissions, sp_dts_getpackage resides in msdb database.

    M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

  • MattF

    SSCrazy

    Points: 2285

    Thanks for the workaround - assigning the db_dtsOperator permissions to the Guest a/c works Ok.

    Something must have happened with the permissions for the sysadmin user, because it has dbo access to MSDB (tried that early on). Also, the equivalent user on our prod box does not even have db_dtsOperator but the package runs Ok.

    Thanks again.

    Matt

    MattF

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    Please I need help about this problem

    I don't understand the solution

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    reem_kn (2/28/2011)


    Please I need help about this problem

    I don't understand the solution

    To execute a package on the server, the user has to have permissions to execute the system stored procedure 'sp_dts_getpackage'.

    Normally this is done by assigning the user the security role db_dtsOperator in the MSDB database.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    Thank u

    I tried it but not work

    I'll told u what I want

    I call Package in c# code then execute this package

    and in db in integration services in msdb in packge roles i choose db_dtsOperator

    Is that Right???

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    reem_kn (3/1/2011)


    Thank u

    I tried it but not work

    I'll told u what I want

    I call Package in c# code then execute this package

    and in db in integration services in msdb in packge roles i choose db_dtsOperator

    Is that Right???

    So you are executing the SSIS package from a C# application?

    How exactly are you doing that?

    Do you get any errors? If yes, please post them here.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    No,Asp.net Application

    Package package = app.LoadPackage("c:\\documents and settings\\reem\\my documents\\visual studio 2005\\projects\\transformation\\transformation\\All_phones.dtsx", null);

    DTSExecResult result = package.Execute();

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    and then I tried this

    app.LoadFromSqlServer("\\localhost\\All_phones", "localhost", null, null, null);

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    and I grant All_phones Package the role:db_dtsOperator

  • Phil Parkin

    SSC Guru

    Points: 244107

    Please post the text of the error messages you are receiving.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • reem_kn

    Mr or Mrs. 500

    Points: 505

    The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (EXECUTE permission denied on object 'sp_dts_getpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Runtime.InteropServices.COMException: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E09 (EXECUTE permission denied on object 'sp_dts_getpackage', database 'msdb', schema 'dbo'.). The SQL statement that was issued has failed.

  • Phil Parkin

    SSC Guru

    Points: 244107

    Well, that seems fairly clear-cut.

    You need to

    GRANT EXECUTE ON MSDB.dbo.sp_dts_getpackage to

    where is the name of the (SQL) user executing the package.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    In the LoadFromSQLServer method, you don't specify values for the username and password.

    Which means that you are using Windows Authentication. Can your ASP.NET application log in with windows authentication and does it have the necessary rights as detailed by Phil?

    If not, you need to create a login and user in your SQL Server and assign that user to the dts_operator role in the MSDB database.

    Then use that user in your application to get the package.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 29 total)

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