I have an SSIS package that connects to both a SQL Server instance and a MySQL instance so it can feed data from the SQL Server to the MySQL instance. When I deployed this package to the SQL Server and ran it from SSMS, everything went fine. When I tried to run this package as a Job where it was being run by the SQL Server Agent Service Account, it broke. I found out that this was because I was using EncryptSensitiveWithUserKey as the package protection level, and that it will always fail unless I am the one running it. Okay.
So among the other protection levels, I am instead trying to use EncryptSensitveWithPassword. In my SSDT that I run from Visual Studio, I do not have ServerStorage as an option for a protection level.
This opens up a different problem for me. Once I deploy the package to SQL Server and run it using SSMS, How do I insert the password to the job? I have seen the command line as an option. Is this the only way to do it in SSMS?
If I require the command line being used as an option, then it requires the package source be the SSIS Package Store rather than the SSIS Catalog. This ends up being a problem if I get 'Class not registered' errors when trying to connect SSMS to Integration Servers — but this is beside the point.
Having had only several weeks of exposure to SSIS, the underlying conceptual question I am wondering is, what are my options for (and how do I go about) getting a MySQL connection enabled in an SSIS package on SQL Server for a user other than myself?
(FYI, I am using SQL Server 2014 and it has Integration Services 2005 on it, and I understand the version mismatch causes the 'Class not registered' errors.)
Addition: Is there any completely different ways to supply a datasource password to a package run by the SQL Server Agent? If I choose DontEncryptSensitive as the protection level, is there a way to supply the datasource password within the job step properties?