Sql-server – Package run on SQL Server Job Agent – Login failed for user

Does your SSIS package contain a DateTime type variable?

Read this Microsoft support article:

You receive an error message when you try to load an SSIS package that contains a DateTime type variable in SQL Server 2005

This problem occurs because the value of the DateTime variable is saved in string format when you save the SSIS package. However, the string representation of the DateTime variable is based on the date format that is specified in the Windows locale. Therefore, when you try to load the SSIS package on a computer that has an incompatible locale date format, the operation fails.

Solutions:

Manually update the DateTime type string in the SSIS package to use the slash (/) character as the date separator

or

Update the date separator in the Windows locale setting to be identical to the date separator of variables in the SSIS package file.

The problem is related to the encryption warning but that is not the cause of the failure. When you encrypt with a user key then only that user can decrypt the password in the connection string, otherwise the connection string will have no password. When you don't save sensitive data the password will not be stored in the connection string so you need to set up an SSIS configuration to supply the proper connection string. Open the package and select SSIS, Package Configurations. In the next screen you will select new and then create an XML based configuration file. When asked which properties you want to configure select the connectionstring property of your oracle connection manager and create the dtsConfig file somewhere on your file system. After you create the file and the configuration you can remove it since you may not want the package to look in the same path for the file.

Edit the dtsConfig file and add the password to the connectionstring and then save the file. Put the file where you want it to live and then go into thte properties of the job step that runs the SSIS package. Select the configurations tab and then browse to the dtsConfig file. This should apply the configuration at runtime and the error about the null password should go away and be be replaced by either a different error or it will work.