SSIS package test connection works when server has VS installed but not establishing connection when build deployed on non-VS server?

0
votes

Though it is not a good practice, when deploying SSIS package, I open Visual studio in remote server and execute test connection to establish successful running of SSIS package. Then SQL jobs executes well without any flaws.

When i deploy SSIS build to my non Visual studio in remote server, my sql jobs throwing errors when i try running my SSIS package.

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 11.0"  Hresult: 0x80040E4D  
Description: "Login failed for user 'myusername'.".  
End Error  Error: 2019-12-26 06:55:07.44     
Code: 0xC020801C     
Source: Data Flow Task OLE DB Source [119]

The same error never happens once i establish test connection via Visual studio. The error is so generic that browsing various solutions online not helping me much.

1
sql-serverssissql-job
Packages always have passwords removed, so you need a mechanism to populate your package with a password. What ProtectionLevel are you using? – Nick.McDermaid
[Do not save sensitive data] protection level – timblistic
If you're using SQL authentication, then this means that the password is removed from the package. You need to parameterise it and populate it at run time. This has most of the info you want. sqlshack.com/… – Nick.McDermaid
Thanks @Nick.McDermaid, Package protectionlevel due to business requirement need to be changed to Encrypt sensitive data with password. So have added commandline /decrypt "myp@$sword" in my sqlagent solved the issue. – timblistic
Now you have a free text password in your command line. – Nick.McDermaid

1 Answers

1
votes

I thinks is User and login issue ,you don't have sufficient login access to run the job or you must have DB owner to run the SQL Agent job ,please try below it might help to resolved your problem .

Previously I had also faced same issue then I have checked it in details step by step and found that I don't have sufficient access at remote desktop to run that sql job.

[https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a98238c-9171-4c87-84b3-f03c143e31d6/login-failed-for-user?forum=sqlintegrationservices][1]