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

Last period as initial value but still have period as parameter?

Hi all,

I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.

Is there any solution to this problem?

Any help will be appreciated.

If you know MDX, then don't use the query designer and just write your own queries. You'll need to have 2 maybe 3 datasets:

Main query dataset

Last available Value from Last n Years(OLAP, MDX)

Hi,

We have a requirement in which data for a member if not avbl. need to be replaced by the latest data avbl. in the last 'n' previous members and we need to control the number of previous members('n') that can be traversed.
ie..

if we are querying data for 1999 and we give traversal parameter of 3 yrs, in case of non avbl. data for 1999, the next avbl data between 1999-1997 should be placed there. In case of traversal year being 4 then the search range changes to 1999-1996.

Year--- DataSet1 --- DataSet2
1996--- 1 ------ 1
1997--- N/a ------ 2
1998--- N/a ------ N/a
1999--- N/a ------ N/a

Result Set --- YEAR --- (Traverse 3years) ---(Traverse 4years)

Data Set 1 --- 1999 --- N/a --- ------ 1
Data Set 2 --- 1999 --- 2 --- ------ 2

We used the fallowing query...but it is throwing the recursion error.

With Member [Measures].[Last Updated]AS 'tail(
Filter ( LastPeriods(3,[Time].Currentmember),
Not isEmpty([Measures].[Observation Value])
),1).item(0)'

Member [Measures].[Last Updated1]AS'
iif (NOt isEmpty([Measures].[Observation Value]),[Measures].[Observation Value],([Measures].[Last Updated]))'

Select
Crossjoin ({[Series].[All Series].[Current account].[BM.GSR.FCTY.CD],[Series].[All Series].[Travel & tourism].[ST.INT.XPND.MP.ZS]},{[Measures].[Observation Value],[Measures].[Last Updated1]}) on columns,
({[Time].[All Time].[1996],[Time].[All Time].[1997],[Time].[All Time].[1998],[Time].[All Time].[1999]}) on rows
from Test1
where([Country].[All Country].[IND])

Any help is highly appreciated.

Thanks and regards

RajaWhat is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?|||Thaks for responding..

1. N/A is Not Available. It means for these combination of dimensions there is no data.

2. I am writing a query like, for each member what is the value available in cube and what value it has been replaced with (in case of n/a cases).

Originally posted by Brett Kaiser
What is N/A? Null?

Also, you're trying to mix and match rows...do you want the MAX?

Large Inserts, TempDB Growing

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.
The source tables, and the OLAP table are in different databases.
Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables...
No ORDER BYs... no GROUP BYs
The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.
Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?
any ideas?
thanks
-MarkMark,
Maybe add a WHERE clause and perform the INSERT in multiple parts.
HTH
Jerry
"Mark" <AnonymousPerson12345@.gmail.com> wrote in message
news:1127429078.936480.53900@.o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
>|||Mark wrote:
> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.
David Gugick
Quest Software
www.imceda.com
www.quest.com