Hello all:

I have a package that I am executing from code. The relevent snippet:


Private pk As Microsoft.SqlServer.Dts.Runtime.Package (declared at form

level)


Sub ExecutePackage
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
pk = app.LoadPackage(PACKAGEPATH, Nothing)
Dim lSource As String = someconnectionstring
pk.Connections(SOURCE).ConnectionString = lSource
pk.Connections(DESTINATION).ConnectionString = destDB
Dim lVar As Microsoft.SqlServer.Dts.Runtime.Variable =
pk.Variables.Item(DATEVAR)
lVar.Value = lastDate
Dim result As Microsoft.SqlServer.Dts.Runtime.DTSExecResult =
pk.Execute()
....
End Sub


When I run this as you see it, the package executes beautifully.
However, I would like some progress reports on it from time to time.
Therefore, I created a class called "PackageEvents" which inherits from

DefaultEvents. I declared a public event in this class, thus:


Public Event ProgressEvent(ByVal eventMessage As String)


I then overrode the OnProgressEvent of the base DefaultEvents class,
and put this in the sub:


RaiseEvent ProgressEvent(String.Format("{0}; {1}: {2}%", taskHost.Name,

progressDescription, percentComplete))


I declared in my main class (which is a form) a variable of type
PackageEvents:


Private WithEvents pkEventHandler As PackageEvents


and then added a handler for its ProgressEvent to output the message to

a textbox.


I therefore changed the code that executes the package as follows:


pkEventHandler = New PackageEvents()
Dim result As Microsoft.SqlServer.Dts.Runtime.DTSExecResult =
pk.Execute(Nothing, Nothing, pkEventHandler, Nothing, Nothing)


When I run this, the package now fails. On the first set of queries, I
get the following error message:


"Operation is not valid due to the current state of the object.".
Possible failure reasons: Problems with the query, "ResultSet" property

not set correctly, parameters not set correctly, or connection not
established correctly.


I have tried a number of things, and am getting nowhere. Can someone
point me in the right direction?


Thanks much,


zdrakec

Update Statement
Date Range
xp_cmdshell - works with string in one format but not in another -
SQL query
How to concatenate strings from a column into a single row (with NULL value)?