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.

ExecuteScalar() Not Returning Value?

Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.

I noticed that this chunk of code is not producing a value...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.

SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1

This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.

Does anybody have any ideas?

Try

WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')

Jos

|||

That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.

But, thanks to your suggestion, what I ended up with was...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Which works "as intended". Thanks!|||

execute scalar returns firts column from firts row of returned data so use this:

SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

you can also use:

ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

select 1

else

select 0

which can work faster if you have more than one record whcih meet your criteria

I hope that it will work

sql