Calling a Dts package from VB.NET

hi,

i'm trying to call a dts package from vb.net.

i got 2 examples which both don't work.

first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error.

source code:
Dim serverName As String = "SERVERNAME"
Dim oPackage As New DTS.Package()
Dim oStep As DTS.Step
Dim pVarPersistStgOfHost As Object = Nothing

oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
"DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost)

For Each oStep In oPackage.Steps
oStep.ExecuteInMainThread = True
Next

oPackage.Execute()

Dim err As Long
Dim source, description, message As String
For Each oStep In oPackage.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo(err, source, description)
message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source,
description)
Else
message = "Success"
End If
Next

oPackage.UnInitialize()
oPackage = Nothing

second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface.

source code:
Dim oPackage As New Package2()
Dim oConnection As Connection2
Dim oStep As Step2
Dim oTask As Task
Dim oCustomTask As BulkInsertTask

oConnection = oPackage.Connections.[New]("SQLOLEDB")
oStep = oPackage.Steps.[New]()
oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")
oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error

With oConnection
.Catalog = "CATALOG"
.DataSource = "SERVERNAME"
.ID = 1
.UseTrustedConnection = True
.UserID = "USERID"
.Password = "PASSWORD"
End With

oPackage.Connections.Add(oConnection)
oConnection = Nothing

With oStep
.Name = "InsertGemal"
.ExecuteInMainThread = True
End With

With oCustomTask
.Name = "InsertGemal"
.DataFile = "D:\ImportGemal.dat"
.ConnectionID = 1
.DestinationTableName = "Gemal"
.FieldTerminator = ";"
.RowTerminator = "\r\n"
End With

oStep.TaskName = oCustomTask.Name

With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With

oPackage.Execute()

oPackage.UnInitialize()
oPackage = Nothing

any help highly appreciated!

t.i.a.,
ratjetoes.

Then you need to use xp_cmdshell by granting a proxy account for SQL Server Agent the right permissions to run the package through Asp.net. The links below covers the extended stored proc xp_cmdshell and the proxy account. One more thing the DTS site is run by a DTS expert there are many resources you could use. Post again if you have more questions. Hope this helps.

http://forums.asp.net/thread/1440879.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

|||

tnx 4 your help,

it was indeed somehting with permission rights.

i got it working now.

ratjetoes.

|||

ratjetoes:

tnx 4 your help,

it was indeed somehting with permission rights.

i got it working now.

ratjetoes.

I am glad I could.