Executing an SSIS package containing a Data Mining Query task from a SQL job

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.

There is a comprehensive KB article that may cover your question:

http://support.microsoft.com/kb/918760

|||

Thanks, that has helped.

for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.

this is the only option on the KB article that worked for me.

Thanks.