0

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?

Improve this question
4
  • Are you using package or project deployment?
    – Antoine Hernandez
    Commented Aug 31, 2016 at 15:08
  • @RDFozz The answer is yes, sorta. I ended up going a completely different route to solve this and never got to the bottom of the original issue. My suspicions are that (1) the user I was trying to run for did not have permissions to access the MySQL ODBC driver and (2) the ODBC data source may have been at the user level instead of the system level. I took an entirely different approach where I created a domain-level service account on the machine I deployed to, installed Visual Studio on that machine, and opened the SSIS project and deployed it as that service account. This fixed it.
    – Michael Plautz
    Commented Feb 16, 2018 at 17:05
  • @MichaelPlautz - You know, you can post that yourself as the answer to the question. You can accept the answer (you may have to wait a couple of days - not sure if the age of the question would be a factor or not.
    – RDFozz
    Commented Feb 16, 2018 at 17:36
  • @RDFozz thanks, I already thought about it, but again, I never actually solved the problem of my original post. I chose an entirely different method where I didn't have to run the package as a user that was different than the user who deployed it. One day I may figure out the root cause of that issue.
    – Michael Plautz
    Commented Feb 16, 2018 at 20:50

2 Answers 2

Reset to default
1

You can try following:

  1. 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.

  2. You can try to execute the package through T-SQL code (there are stored procedures for this).

  3. Try the command line option in agent to execute the dtexec to run the package.

Improve this answer
2
  • 1. I had thought about doing this, but the project is deployed on a different machine than the SQL Server, and the SQL Agent is local to that server. 2. That sounds scary and non-standard, but I'll keep it as a backup. 3. Would you elaborate? I don't know enough about dtexec's command line to properly invoke my package (which was deployed to the Integration Services Catalogs), but this seems like the way I will want to go.
    – Michael Plautz
    Commented Aug 29, 2016 at 18:42
  • You can provide the encrypted password to dtexec - msdn.microsoft.com/en-us/library/hh231187(v=sql.120).aspx First test the dtexec code with password in cmd and then implement it with sql agent.
    – Pintu Kawar
    Commented Aug 29, 2016 at 19:05
0

When I have deployed SSIS packages that use "EncryptSensativeWithPassword" using the Package source of "file system" as soon as I browse to the Package initially or subsequently change "Set values" I would get prompted to enter the password.

Recently, this did not occur when moving to a new Server. I thought it was a bit odd, but did not ponder on the situation further until all of my password protected packages started failing. The error was not super helpful either. "The package is encrypted with a password. The password was not specified, or is not correct"

I could get it working via Visual Studio, but that was it. Nothing else had changed, so what could it be? Turns out that if you don't grant the Proxy Account Full rights to the Package (*.dtsx) this is what happens.

I'm hopeful that this post will be found by someone else and it helps them.

Improve this answer
1
  • This doesn't feel like an answer to the question (it seems to presuppose an answer, as you indicate you were prompted to provide a password, and could presumably run your jobs on your old server). If you want to provide this as a useful piece of advice, I'd write it up as a new question, and self-answer it - it would probably be easier to find that way. Basically, put everything up to "Turns Out" in as the question (with a bit more framing detail, as you need to make the context clear), then make the rest the answer (again, more detail would be good).
    – RDFozz
    Commented Jan 12, 2018 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.