I have an SSIS package that takes data from a MySQL database and puts it in to a SQL Server table. The connection to MySQL is ADO.net. The package runs fine in BIDS.
I would like to deploy the package to be run as a Job on SQL Server 2008. The MySQL connection requires sensitive data to be either stored within the package or held in some external configuration source. If it is stored within the package it will either by encrypted with a key specific to my windows user profile or must be protected via a password.
I have tried three methods of setting up a SQL Server Agent job to execute this pacakge, and all fail:
Using a Password
I set the ProtectionLevel of the package to EncryptSensitiveWithPassword and supply a password for the package. I then save a copy of the package to the msdb. I can then connect to SSIS and run the package, at which point I am prompted for a password.
When I try to schedule this as a job in SQL Server I am prompted to enter the password by clicking on the Configurations tab of the Job Step Properties and I can see that the /DECRYPT switch has been added to the Command Line tab. The Run As property is set to the SQL Server Agent Service Account, which is mapped to a database login that has sysadmin server role. When I attempt to start this job I get the following error:
Executed as user: DOMAIN\UserROLE. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:31:30 Error: 2012-07-03 12:31:31.20 Code: 0xC00291EC Source: Get Data Execute SQL Task Description: Failed to acquire connection "DATA_SOURCE". 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
It seems that using this method the package password itself is not stored anywhere for the job to access it.
Using an XML configuration file
I then created a copy of the package and set the ProtectionLevel to DontSaveSensitive and I set up a package configuration to store the MySQL credentials in an XML file. I then save this to the msdb and then when I run the package from Integration Services I can provide a path to the config file, and all is well. However, once again when I set up a job for the package I point to a configuration file on the server but the job fails with the same error. I am wondering whether this is because the SQL Server Agent does not have an associated windows account that would allow it to read from the XML file stored in the file system.
Using a SQL Server table configuration
This time I created a copy of the package and set the ProtectionLevel to ServerStorage. I then created a [SSIS Configurations] table in the target SQL Server database. I modified the entries in this table to contain the password for the MySQL data source. Again, the package runs successfully from Integration Services, but fails with the same error as previously when run from a job. I have checked that the SQL Server Agent is able to read from the [SSIS Configurations] table ok - I set up a standalone job to test this.
Does anyone have any suggestions about what else I should do here?
I am thinking of giving up on SQL Server Agent Jobs for this and going back to having the package scheduled simply by the windows scheduler but thought before throwing in the towel I would try here first.
