I have an SSIS package that has been running as a job without issues for years, alongside several other jobs.
Recently we changed our subnet IP address - this along with one other job was referencing the SQL Server by IP address so it broke. Which made sense.
However, when I corrected the connection string by switching the IP address to the server name, it still did not work. There are other jobs referencing the server by name and not IP address, and those have no issues.
What am I missing?
This is the connectionstring I'm using:
Data Source=[servername];User ID=SQLDTS;Initial Catalog=Policyholder;Persist Security Info=True;MultipleActiveResultSets=True;Application Name=SSIS-JOB_TEMP_TABLE_CLEANUP-{[unique ID of connection string in package]}DEVSVR.Policyholder.SQLDTS1;
There are jobs with this identical connection string with only the application name being different. The packages have the same permission sets.
Here is the error I get:
Execute SQL Task Description: Failed to acquire connection "DevSvr.Policyholder.SQLDTS1". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:11:37 AM Finished: 8:11:37 AM Elapsed: 0.687 seconds. The package execution failed. The step failed.
The issue here was simply that the password was missing. I did not realize it was part of the connection string because SQL Server removes it from the edit window after you save. Even if changing an unrelated aspect of the connection string it needs to be re-added.