executing dts package through asp.net creates tables but no data!

hey out there,

just started playing with this so bare with me...

i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables.

it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data!

this is my code:

1Public Sub executeDts()23Dim oPkgAs DTS.Package24 oPkg =New DTS.Package25Dim oStepAs DTS.Step6 Dim sMessageAs New StringBuilder789 oPkg.LoadFromSQLServer("serverNameHere","userNameHere","passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , ,"packageNameHere")1011For Each oStepIn oPkg.Steps1213 sMessage.Append("<p> Step [" & oStep.Name &"] ")1415If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_FailureThen1617 sMessage.Append(" failed<br>")1819Else2021 sMessage.Append(" succeeded<br>")2223End If2425 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description &"""</p>")2627Next2829 Response.Write("sMessage = " & sMessage.ToString &"<br/>")3031 oPkg.Execute()3233 oPkg.UnInitialize()3435 oPkg =Nothing3637 End Sub38

am i missing a step?! it seems very odd that the tables are created but the insert fails...

any advice anyone can offer would be great!

cheers,

jake

hi jake the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

|||

hi jake

the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

sql

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.