Post reply

SSIS SQL Destination Using Windows Username Instead Of Connection String User

  • October 9, 2008 at 4:41 pm

    #126963

    For the past two days, I've been troubleshooting an issue we have with an SSIS package that was just created. If I open the package when running BIDS as administrator, everything runs fine. If I open the package in any other way or attempt to execute it from the Management Studio after deploying, I get the following errors:

    Error: 0xC0202071 at Import Log File to Raw Log, SQL Server Destination [840]: Unable to prepare the SSIS bulk insert for data insertion.

    Error: 0xC004701A at Import Log File to Raw Log, DTS.Pipeline: component "SQL Server Destination" (840) failed the pre-execute phase and returned error code 0xC0202071.

    I've read several solutions to this issue. A work-around is to use an OLE DB Destination. This works for us, but I'd really like to have the option of using SQL Server Destinations in the future. I understand that the user that the package is running as must have the "Create Global Objects" permission and that the SQL Server Destination must be pointed to the local machine. I've made sure that both of these qualifications have been met. The connection string that the OLE DB Connection Manager is using is as follows:

    Data Source=localhost;User ID=sa;Initial Catalog=xxx;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;Use Encryption for Data=False; After running a trace in SQL Server Profiler, I noticed the following discrepancy:

    EventClass ApplicationName NTUserName LoginName Running as Administrator ExistingConnection MS Visual Studio 2005 sa Running as anything else ExistingConnection MS Visual Studio 2005 myusername MYDOMAIN\myusername I suspect the fact that since the package isn't logging in as 'sa', that's the reason that everything's failing. Am I wrong? And if I'm not, how can I fix this issue?

    FYI, I'm posting for someone else. Please respond if you have an answer.

  • October 29, 2008 at 1:08 pm

    #891436

    Hi,

    I hope you have selected the target load type as "Table or View Fast Load", so can you select access mode as "Table or View" in

    OLE DB Destination editor and try .

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply