Error on Acquiring Connection in SSIS package using Connection string from Configuration File

0

I am getting below error during execution of SSIS Package which is running by a Sql Schedule Job and using a config file for DB Connection. Job is running in a different domain user account which has admin privileges. SSIS package Protection level is DONT SAVE SENSITIVE.

Executed as user: domainname\user. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6560.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  11:59:25 PM  Error: X/X/XXXX 23:59:27.73     Code: 0xC0202009     Source: XYZ Connection manager "DB_1"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'USR_1.".  End Error  Error: X/X/XXXX 23:59:27.73     Code: 0xC020801C     Source: Action1 source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DB_1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: X/X/XXXX 23:59:27.73     Code: 0xC0047017     Source: Action1  SSIS.Pipeline     Description: component "CC source" (1) failed validation and returned error code 0xC020801C.  End Error  Error: X/X/XXXX 23:59:27.73     Code: 0xC004700C     Source: Action1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: X/X/XXXX 23:59:27.73     Code: 0xC0024107     Source: Action1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:59:25 PM  Finished: 11:59:27 PM  Elapsed:  1.966 seconds.  The package execution failed.  The step failed.

Sql User USR_1 is used during Package Design Time. But DB Connection String has different Sql Login in Config File USR_2. This error Start to come after changing the Login Password for Sql User USR_2. But We are able to Connect SQL MGMT Studio with USR_2 with New Password. And When we use USR_1 user and its password in COnfig File. This error does not come.

sql-server-2008
ssis
connection-string
asked on Stack Overflow Jul 5, 2018 by AsifQadri • edited Jul 5, 2018 by jarlh

1 Answer

0

I have found root cause behind this issue. Some unwanted characters were present in the password. [] () , ; ? * ! @ =.

answered on Stack Overflow Sep 29, 2020 by AsifQadri

User contributions licensed under CC BY-SA 3.0