SSIS SQL Destination Using Windows Username Instead Of Connection String

• Crawford

  SSC Rookie

  Points: 41

  More actions

  October 9, 2008 at 6:35 pm

  #124095

  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 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?