Post reply

Error running SSIS package

  • March 31, 2008 at 8:15 pm

    #379518

    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

  • April 1, 2008 at 9:23 am

    #796609

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

    Greg

  • April 1, 2008 at 10:12 pm

    #796998

    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

  • April 1, 2008 at 10:34 pm

    #797007

    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

  • February 28, 2011 at 9:50 am

    #1292415

    Please I need help about this problem

    I don't understand the solution

  • March 1, 2011 at 2:28 am

    #1292764

    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

  • March 1, 2011 at 3:49 am

    #1292798

    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???

  • March 1, 2011 at 3:54 am

    #1292801

    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

  • March 1, 2011 at 4:40 am

    #1292815

    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();

  • March 1, 2011 at 4:42 am

    #1292817

    and then I tried this

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

  • March 1, 2011 at 4:49 am

    #1292818

    and I grant All_phones Package the role:db_dtsOperator

  • March 1, 2011 at 4:52 am

    #1292820

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 1, 2011 at 8:42 am

    #1292991

    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.

  • March 1, 2011 at 9:19 am

    #1293022

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 1, 2011 at 11:41 am

    #1293105

    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 28 total)

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