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
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.
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.
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:
- Click Start, point to Administrative Tools, and then click Local Security Policy.
- Expand Local Policies, and then click User Rights Assignment.
- In the right pane, double-click Create global objects.
- In the Local Security Policy Setting dialog box, click Add.
- In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK.
- Click OK.
- Note: When using an account that is not a member of local Administrators group, UAC does not come into play.
- 922708 How to use User Account Control (UAC) in Windows Vista
- 975787 Guided Help: Adjust User Account Control settings in Windows 7
- How do I run an application once with a full administrator access token?
- What happened to the Run as command?
Steps to reproduce:
- Create a SSIS Package on an OS with UAC ON.
- Create a Dataflow task. Connect any Source component to a SQL Server Destination component to export data from the source to the local SQL Server3. Execute the package from BIDS.
Note: The package would work only if it is run under the Built In Administrator User that gets created during OS installation. It would fail for any other user explicitly added to the Local Administrator group thereafter.
MS Only solution: This solution is moved to MS Only as it talks about disabling UAC.
Warning This workaround may make a computer or a network more vulnerable to attack by malicious users or by malicious software such as viruses. We do not recommend this workaround but are providing this information so that you can implement this workaround at your own discretion. Use this workaround at your own risk.
Note We strongly recommend that you do not run Windows Vista when the UAC feature is disabled. However, you can temporarily disable it and then enable it after you install the service pack for Integration Manager.
1. Click Start , type accounts in the Start Search box, and then click User Accounts in the Programs list.
2. Click Turn User Account Control on or off . If you are prompted for an administrator password or for confirmation, type the password, or provide confirmation.
3. Click to clear the Use User Account Control (UAC) to help protect your computer check box, and then click OK .
4. Restart the computer.
After the computer is restarted, apply the service pack, and then enable the UAC feature. To enable the UAC feature, recheck the Use User Account Control (UAC) to help protect your computer check box, and then restart the computer again.
Product Bug Number: Defect VSTS81162
Author ID (email alias):desarkar
Writer ID(email alias):ramakoni
Tech Review ID (email alias):sqlprev
Confirm Article has been Tech Reviewed: Yes/No
Confirm Article released for Publishing: Yes/No
Artikelnummer: 2009672 – Letzte Überarbeitung: 22.10.2010 – Revision: 1