Thursday, January 26, 2012

Unable to prepare the SSIS bulk insert for data insertion error

You might encounter this error if you have an SSIS package with SQL Server Destination component in the Data Flow task.

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.

This error occurs if the SQL Server Destination component is trying to access a SQL Server Database on a remote server(You can’t use SQL Server destination to access a database on a remote server). To resolve this, either replace your SQL Server Destination component with OLE DB Destination component or run the package on the same machine as your SQL Server Destination.

What if you are on the on the same machine as your SQL Server Destination and you still get this error? Well, this could be due to User Account Control (UAC). You can try one of the options below to resolve this:

Solution 1: Launch the tool you are running the package from as Administrator. For example, if you are running the package in BIDS, go to Start > All Programs > SQL Server 2005 or SQL Server 2008, right-click Business Intelligence Development Studio and select “Run as administrator” as shown below.

Second solution: Grant "Create Global Objects" user right to the account you are using. Go to Start > Administrative Tools > Local Security Policy > User Rights Assignment > Create global objects and add the user account you are using and click ok. Please note that this account should not be a member of  the local Administrators group.

If you are on SQL Server 2008 R2 or later, the error would be: Unable to bulk copy data. You may need to run this package as an administrator.

Hope this helps!

1 comment:

  1. I had the similar issue.

    By re-opening the SSIS interface with Admin account the issue was resolved.