Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS (dtexec) and bulk load permission Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 2:14 AM
Points: 7, Visits: 18
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
Post #1511766
Posted Wednesday, November 6, 2013 3:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:23 AM
Points: 856, Visits: 1,504
check if BulkAdmin rights are assigned to the user which you are connecting to database.
Post #1511778
Posted Wednesday, November 6, 2013 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 2:14 AM
Points: 7, Visits: 18
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
Post #1511802
Posted Wednesday, November 6, 2013 4:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1511805
Posted Wednesday, November 6, 2013 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 2:14 AM
Points: 7, Visits: 18
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

Post #1511853
Posted Wednesday, November 6, 2013 10:16 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:23 AM
Points: 856, Visits: 1,504
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.
Post #1512085
Posted Wednesday, November 6, 2013 11:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 2:14 AM
Points: 7, Visits: 18
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...

-- Satu
Post #1512098
Posted Thursday, November 7, 2013 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 2:14 AM
Points: 7, Visits: 18
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.
Post #1512148
Posted Monday, November 11, 2013 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
Great. Glad you got it solved and thanks for posting back.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1513227
Posted Tuesday, November 12, 2013 3:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:23 AM
Points: 856, Visits: 1,504
Koen Verbeeck (11/11/2013)
Great. Glad you got it solved and thanks for posting back.


indeed ...
Post #1513378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse