CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER when running .dtsx from server

-1

I have an SSIS project that seems to work properly when ran from local machine, but when I deploy it to the server, and run it I get the following errors:

Package:Error: 
SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21."   
"Data Flow Task:Error: 
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager 
""RussiaServer"" failed with error code 0xC0202009.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The Message source name is Data Flow Task and package. Data flow task has a SQL command inside it, that selects rows basing on parameters (someone suggested in one of the other threads it might be due to date data type? but I had the same data type as in answer).

Also - this package worked properly on previous server (MS 2012).

Things I've tried:

  • setting delayvalidation to true on connection manager and data flow task (i don't see it on any other child objects)
  • setting tun64bitRuntime to false changing deployment server version (2010 and 2016)
  • Trying to validate package, without running in Managment studio - it validates successfully? even though it falis on validation when running normally

Things I couldn't try:

  • Installing access database engine for x86 - someone suggested doing that, but unfortunately it cannot be installed when using 64bit office, which has to stay installed.
sql-server
ssis
ssis-2012
asked on Stack Overflow Aug 1, 2018 by Xyzk • edited Aug 1, 2018 by Xyzk

2 Answers

0

You have to change the protectionlevel to EncryptSensitiveWithUserKey in the package properties. It is working locally. But, when you try to deploy it to server, the passwords are not saved and not able to connect.

Read more on this Securing SSIS Package

answered on Stack Overflow Aug 1, 2018 by Venkataraman R
0

Please use variables for connection string. and while deploying it use this variable.

Thanks Sasi

answered on Stack Overflow Aug 14, 2018 by Bhushan

User contributions licensed under CC BY-SA 3.0