I have an SSIS package with two OLEDB connections. The package copies data from one server to another using the same database name. I have two variables called FromServer and ToServer. I set an expression for Server in the OLEDB connections using the values from the variables. The package runs fine if running from within Integration Services.
Now, I want to call this package from a .NET application. I get the values for the servers from user input. I pass the values into my SSIS package but it always uses what is already set in the pagkage variables instead of what I am passing in. If anybody can shed some light on this I would appreciate it. I searched the web and haven't been able to find anything that works. Thanks.
objDTS = New DTS.Package
pkg = app.LoadPackage(_DTSPackageName, Nothing)
pkg.Variables.Item("DBname").Value = _DatabaseName
pkg.Variables.Item("SourceServer").Value = _FromServer
pkg.Variables.Item("DestServer").Value = _ToServer
ChangeAllStepsToRunOnMainThread()
Try
pkg.FailParentOnFailure = True
pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)
If pkgResults <> DTSExecResult.Success Then result = 0
Return result
Catch ex As COMException
strError = ex.Message
Return 0
Finally
objDTS.UnInitialize()
End Try