Error running simple SSIS package

• Jason Wilks

  Old Hand

  Points: 389

  More actions

  October 3, 2020 at 12:14 pm

  #3792673

  I have a simple package that loads a small staging table from a CSV file.

  I create the table (succeeds), truncate it, then reload it via OLE DB.  It fails on truncate table SQL task.

  [Execute SQL Task] Error: Failed to acquire connection "WIN-3SVP8BUQIEM_SQL_2016.AdventureWorksDW.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.

   

  I am using an OLE DB connection.  In the connection manager, the 'Test Connectuion'  succeeds, so I am not sure why it is having an issue connecting when executing the package.

  I am running the package from inside Visual Studio 2017 on a windows 7 host.  The database server is a named instance residing on a VMware VM running windows server 2012 r2.   I can connect to the database from SSMS from my win 7 host no problem.  The firewall is disabled.  Distributed Transaction Coordinator services are enabled on both host and VM.

  Any ideas as to what I can do to troubleshoot this.  TIA, Jason

• Jason Wilks

  Old Hand

  Points: 389

  More actions

  October 3, 2020 at 1:06 pm

  #3792678

  Actually I saw there is another error before this (but I didn't see it because it was listed near the bottom of the log).

  Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".

  And the below post resolved the issue... sorry

  https://www.sqlservercentral.com/forums/topic/the-partner-transaction-manager-has-disabled-its-support-for-remotenetwork-transactions