Running a Stored Package from a vb.net app

• Lloyd Bond-383166

  Old Hand

  Points: 343

  More actions

  July 30, 2008 at 7:39 am

  #125327

  Hi,

  I've succesfully implemented an vb.net app that creates a .bat on the server and then the server job runs the bat which passes in dynamic variables created in the vb.net app. It all runs swimmingly.

  The problem is that there are really too many points of failure so I've created a vb.net app which calls Integration Services and passes in dynamic variables (basically file locations) and then runs the package, but I'm having problems, this is the basic code:

  Imports Microsoft.SqlServer.Dts.Runtime

  Dim pkg As New Package

  Dim integrationServices As New Application

  Dim pkgResults As DTSExecResult

  Dim pkgLocation As String = "File System\package_name"

  If integrationServices.ExistsOnDtsServer(pkgLocation, "servername") Then

  pkg = integrationServices.LoadFromDtsServer(pkgLocation, "servername", Nothing)

  Else

  Throw New ApplicationException( _

  "Invalid package name or location: " & pkgLocation)

  End If

  Try

  pkg.Variables("User::Filepath").Value = txtFileLoc.Text.ToString

  pkgResults = pkg.Execute()

  Catch ex As DtsRuntimeException

  MsgBox(ex.ToString, MsgBoxStyle.Critical, "error")

  End Try

  MsgBox(pkgResults.ToString, MsgBoxStyle.Critical, "error")

  pkg = Nothing

  integrationServices = Nothing

  Nothing falls over but the pkgResults.ToString gives me "failure", I know the package is being opened as I've retrieved values for variables back out from it. Can anyone point me in the direction of some sort of error handling (I've added some to the package but none of my variables are being set so I'm guessing that it's falling over before the first task).

  Thanks in advance.

  P.S. I've added the Microsoft.SQLServer.ManagedDTS, DTSRuntimeWrap, DTSPipeLineWrap to the references.