Bulk insert RRS feed

  • Question

  • When I run the qry below for Bulk insert from SQL QA or even use the Bulk insert task in SSIS. It gives me the err below

    You do not have permission to use the BULK INSERT statement.

    Bulk insert statement used is below:

    FROM 'filea.txt'
    WITH (FORMATFILE='formatfile.fmt')

    My questions are.
    1.I am assuming the dql acct I amusing does not have bulk admin role. Why would organizations not provide Bulk admin role? Is there a security issue or something to grant Bulk insert role?

    2.  If BI is not alowed what are my other options. I cannot use Data flow task, because I have to load diff text files with diff structures into SQL. Since DF is limited with metadata cannot use DF. Hence was going towards BI. Howver if that option is not allowed in our org, let me know the other workarounds.

    Tuesday, April 7, 2009 3:39 PM

All replies

  • 1. Yes, it is for security to prevent users from unknowingly flooding the database with massive data inserts if such risk exist depending on the applications and sql users involved.

    2. If I was unable to use BI I would still use bulk insert but in a database where you have that permission (say a local test database) and from that table generate individual line by line insert statements and execute the script on your production database. Its not very beautiful but it should give you a work around at least.

    Friday, April 17, 2009 12:36 PM