SSIS (dtexec) and bulk load permission

  • Hi,

    I am an accidental DBA trying to install existing SQL Server 2005 in a new environment with existing contents. I have done my googling on this but with little help.

    We sometimes use Integration Services packages run straight from Execute Package Utility (dtexec) - so NOT from SQL Server Agent job or other application. Now I cannot get a SSIS package to execute at all, when trying to "Run package". The error is "You do not have permission to use bulk load statement." At this point, the package should bulk load data from a csv file to database table. The csv file location and format are specified in dtsConfig files. Other ETL packages without any "outside" csv files run just fine.

    As you know, using SSIS this way requires authenticating with Windows credentials. SQL Server is installed on drive D. The csv files in question are situated on drive G on the same server. Any jobs or other SSIS packages have not caused any trouble.

    This clearly is some kind of permission issue but what permissions should I give, to whom and where? Advice would be much appreciated.

    -- Satu

  • check if BulkAdmin rights are assigned to the user which you are connecting to database.

  • In database engine I have sysadmin role but when one connects to Integration Services (instead of Database engine) Windows credentials are used. I have gathered that the packages are actually run under the Windows account they are started with. I suspect that this problem has nothing to do with privileges given to any login in database engine, but could be wrong.

    -- Satu

  • Satu (11/6/2013)


    In database engine I have sysadmin role but when one connects to Integration Services (instead of Database engine) Windows credentials are used. I have gathered that the packages are actually run under the Windows account they are started with. I suspect that this problem has nothing to do with privileges given to any login in database engine, but could be wrong.

    -- Satu

    The SSIS packages are run under the credentials by the user who starts them.

    In other words, if you login into SSIS, right click the package and choose execute, the package will run under your permissions.

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

  • Hi and thanks for the response.

    I understand that my Windows account is used for running the package. Yet I can access the required files and do the bulk load and the package cannot. I tried giving my Windows credentials explicitly the administrator rights on the target folder but this did not have any impact. What right is my Windows account missing?

    -- Satu

  • its not the folder access which is causing the issue, its the Bulk insert task which required sysAdmin, BulkAdmin role associated with the User Login.

    you can check it if you remove the bulk insert option and just insert the data in to a random table in your database or write another file from the source file.

  • Hi,

    how can you give a Windows account bulkadmin/sysadmin role in SQL Server so that it is used when running a SSIS package via dtexec?

    I tried adding a login in SQL Server for my Windows account, and giving that account even sysadmin role, but this has no impact.

    This is a surprisingly difficult task! Anyways we probably try and configure these tasks (run earlier straight through Integration Services) as agent jobs so that they can be started remotely. Still - who does like to leave something undone because it cannot be solved... :blush:

    -- Satu

  • SOLVED. Thank you guys for your responses, but this was basically a stupid user exception.

    The problem had after all nothing to do with the Windows account (as you probably suspected). The SSIS package opened a database connection with a SQL Server user which did not have the bulkadmin role. This I finally understood when trying to run the same package as agent job. Magically giving that role solved the bulk load error.

  • Great. Glad you got it solved and thanks for posting back.

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

  • Koen Verbeeck (11/11/2013)


    Great. Glad you got it solved and thanks for posting back.

    indeed ...

Viewing 10 posts - 1 through 9 (of 9 total)

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