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?
Best Answer
You can try following:
With EncryptSensitiveWithUserKey - Deploy the project with the same ID through which the agent job will run (I always use SSIS Proxy account created). Also, change the Job owner to same ID or SysAdmin.
You can try to execute the package through T-SQL code (there are stored procedures for this).
Try the command line option in agent to execute the dtexec to run the package.