Launch DTS with OLAP from .net application

Hi,

with reference to the following thread:

http://forums.microsoft.com/msdn/ShowPost.aspx?postid=740855&isthread=false&siteid=1&authhash=775a583435c503730bf96695fe78ffc77f04a290&ticks=632942355374528292

Which in summary is that I am trying to execute DTS packages containing OLAP tasks to process cubes from an application. It hangs and fails after an hour and some minutes.

the code :

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref VarPersistStgOfHost);

package.Execute();

I have launched the DTS on command prompt using DTSRUN on the computer I am trying to execute my code, and it executes marvellously.

DTSRUN can also execute a DTS on another physical server Alpha for instance which process a cube of AS2000 found on another physical server Beta for instance.

Since I need to check that each steps have been executed properly and that running DTSRUN from the application is not a possibility to do so, I need a way to be able to launch the DTS and get to know which steps is failing. the above code is supposingly a way but its failing on LoadFromSQLServer() method.

I am using SQL Server 2000 and AS2000 and .net 1.1 (modifying a legacy system to enable the above facility).

Any views please?

Thanks

Is there any error message?

If so, can you paste here its full text (replacing any confidential information like server names, IP addresses, user names, passwords etc.)?

|||There is no error page. The steps in the package are with status "failed". Thats it.|||

I am moving this to the "SQL Server Integration Services" forum (successor to DTS), which may be able to help troubleshooting.

|||

Failing on the LoadFromSQLServer method should be quick, and I would expect an exception.

The Execute on the other hand will not raise an exception. Use package logging and/or implement the events interface to get more detail of tasks that fail in the package.

If the LoadFromSqlServer fails, perhaps try another storage location just to see if you can isolate the problem for now.

|||

HTH.

Dim EventsSSIS As Eventos

EventsSSIS = New Eventos()

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

..

..

..

Public Class Eventos

Implements IDTSEvents

Sub OnExecutionStatusChanged(ByVal exec As Executable, ByVal newStatus As DTSExecStatus, ByRef fireAgain As Boolean) Implements IDTSEvents.OnExecutionStatusChanged

End Sub

Sub OnBreakpointHit(ByVal breakpointSite As IDTSBreakpointSite, ByVal breakpointTarget As BreakpointTarget) Implements IDTSEvents.OnBreakpointHit

End Sub

|||

I am using .net 1.1 and SQL server 2000. I do not have SSIS.

The solution is to use Stored procedure to launch it, this relieving this responsibility from the .net application.

there is a call from the stored proc called the LoadFromSQLServer that can be used for this purpose.

Thanks