You may get "Unable to prepare the SSIS bulk insert for data insertion" error on UAC enabled systems

Article translations Article translations
Close Close
Article ID: 2009672 - View products that this article applies to.
Expand all | Collapse all

Symptoms

Consider the following scenario:

  • You create a SSIS package that has a SQL Server Destination component within a Dataflow Task.
  • You try to run this package on systems where User Account Control (UAC) is enabled (for example Vista or Windows 7) using one of the following methods:
    • Business Intelligence Development Studio (BIDS)
    • SQL Server Management Studio (SSMS) Object Explorer
    • DTExec.exe
    • DTExecUI.exe 

In this scenario, you may get an error message that is similar to the following:

SQL Server Destination] Error: Unable to prepare the SSIS bulk insert for data insertion.
[SSIS.Pipeline] Error: component "SQL Server Destination" failed the pre-execute phase and returned error code 0xC0202071.

Note: You will not get this error if you run the package under the Builtin Administrator Account that gets created during Operating System installation. But you will get this message for any other user including those that are members of the Local Administrators group.

Note: The problem does not occur when you execute the same SSIS package as a SQL Server Agent job.

Note:  After SQL 2008 Service Pack 2 is installed, the text of the error DTS_E_BULKINSERTAPIPREPARATIONFAILED (0xC0202071) has been changed to:

 Unable to bulk copy data. You may need to run this package as an administrator.

This change was made in attempt to make it easier to understand the corrective action needed, as described in the resolution section of this KB.

Cause

On systems where UAC is enabled, when an application (like SSIS) is launched by an account that is a member of the Administrators group, it gets two security tokens, one a low privileged token and another an elevated token. The elevated token is only used when the application is explicitly run under an Administrator account by choosing “Run as Administrator” option. By default SSIS always uses the low privileged token resulting in a failure when connecting to a SQL Destination.

Resolution

Use one of the following methods to workaround the problem.:

  • If you are running the package from either SQL Server Management Studio (SSMS) or Business Intelligence Development Studio (BIDS) or DTExecUI.exe, launch those tools under the elevated Administrator account. To do this, click Start , point to All Programs , point to SQL Server 2005 or SQL Server 2008 , right-click the tool you are using and then click Run as administrator. This launches the application with elevated privileges of the Built In Administrator account and the package executes successfully. 

Similarly if you are running the package using DTExec.exe launch it from an elevated command prompt. You can start the elevated command prompt by clicking Start, click All Programs , click Accessories , right-click Command Prompt , and then click Run as administrator.

Note: If you do not log on to the computer as an administrator, you are prompted to provide the administrator account. When you are prompted to provide the administrator account, type the administrator user name and password in the User Account Control dialog box. Then, click OK .

  • Replace the SQL Server Destination components in the Dataflow Tasks which are failing with OLE DB Destination components that point to the same SQL Server connection manager.

  • Use an account that is not a member of  the local Administrators group after assigning "Create Global Objects" user right to that account.
  • To do this, follow these steps:
    1. Click Start, point to Administrative Tools, and then click Local Security Policy.  
    2. Expand Local Policies, and then click User Rights Assignment.  
    3. In the right pane, double-click Create global objects.  
    4. In the Local Security Policy Setting dialog box, click Add.  
    5. In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK. 
    6. Click OK. 
     
    Note: When using an account that is not a member of local Administrators group, UAC does not come into play.

More Information

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2009672 - Last Review: October 22, 2010 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
Keywords: 
KB2009672

Give Feedback

 

Kontaktieren Sie uns, um weitere Hilfe zu erhalten

Kontaktieren Sie uns, um weitere Hilfe zu erhalten
Wenden Sie sich an den Answer Desk, um professionelle Hilfe zu erhalten.