Consider the following scenario:
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.:
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.
Article ID: 2009672 - Last Review: 10/22/2010 19:53:00 - Revision: 3.0