Processing AS2000 Cubes from SSIS

Hi All,

I have a scenario where I want to execute AS 2000 Cubes from a SSIS package. In my Prod environment I have two servers one with the SQL 2000 database and the AS2000 cubes on it and the other with SSIS installed on it.

What I am doing here is I have a DTS package with a process cube task in it, this DTS package is saved as a structured file and then I call this DTS package from SSIS using the Execurte 2000 package task. Just FYI the process cubes task is using local as the server reference. I want to know how this will work when I execute this DTS package from within an SSIS package running on a different server? Appreciate all help.

Thanks

The "normal" way to process AS 2000 from DTS was to use the OLAP Processing task for the job, but that only gets installed as part of AS 2000, and hence will not be available on your SSIS/DTS machine. Using local as the server reference will also fail, unless you run the DTS package on the AS server, as quite obviously the local server is not the AS server, for a package running on SSIS/DTS machine. There is no magic way to get the DTS package to run on the other machine, other than calling the package from that machine itself. I would stick with a DTS package on the AS2000 box.

|||

Thanks Darren,

Actually I do have DTS and AS2000 installed on the same server which also has SQL Server 2000 databases on it, However I am executing these DTS packages (which process the cubes using local as server reference) from SSIS packages which are running on a different server. The DTS packages are saved as structured files on the SSIS server. Hope I am explaining it properly - what my concern is, whether executing these DTS packages having process cubes task within it from a different server will work?

Appreciate your help.

Thanks

|||

I Still have this concern whether my packages will run or not, it would be better to know now rather than getting to know in prod. Any help is appreciated.

Thanks

|||No this will not work. All the AS stuff is on the wrong server, it needs to be on the server upon which the DTS package runs. If calling DTS from SSIS then that means the DTS package runs on the same server as SSIS, which is not the AS server. This is all about execution location, which for both DTS and SSIS is cleint side, they are not client/server like SQL Server.|||

Thanks Darren,

Appreciate your help.

Thanks

|||

Hi,

I have a Cube on my AS2000 server running on the same machine as my SQL server 2000. I can execute the DTS from the SQL Enterprise manager properly.

I need to implement a C# code that will enable me to execute the DTS. I already have one as follows which works on any DTS:

---

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

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

package.Execute();

---

As I said, it works with any DTS, but as soon as I work with one which Processes Cubes, it hangs for about 2 hours, when the cube can normally be processed in 20 seconds, and crash on the following

DTS Execution error:

Do you have a clue why is it so please?

|||

I've been thinking about this some more. As I recall you can process OLAP 200 cubes remotely, well I think you could in DTS, so all you need is -

1 DTS

2 DSO

3 OLAP Task

Now in SSIS we have 1 already. 2 we can get, see the feature pack download on MS site April or November. For 3, the task, it seems that the documenattion says you need to install AS 200 to get this, well as I recall it was actually just a DTS custom task that used DSO, so why not go get the DLL, register it by hand in DTS, and give it a try -

The task DLL should be in X:\Program Files\Microsoft Analysis Services\Bin\msmdtsp.dll on any SQL 2000 box with AS installed.

|||

Darren,

I am using SQL Server 2000. I do not have SSIS and I have to get this sorted way before we upgrade to 2005.

1. Are there any issues with the methods LoadFromSQLServer from the class Package2Class when we invoke a DTS with OLAP Cube.

2. I have already installed Analysis Services. When I deployed my application to a test web server which has SQL client installed, I have a COM exception. Does it mean that I need to have AS2000 installed in order for LoadFromSQLServer to instantiate the correct steps object which are of type OLAP tasks?

Can you please advise me on the coding and permission stuff that will allow a code on .net 1.1 (since it is an upgrade on a legacy system) to launch a DTS on SQL2000 which processes a cube on AS2000?

Thanks

WaaZ

|||

WaaZ, I was replying to the thread from db_guy. I'm not sure why you have posted a DTS question in the SSIS forum, and it is not really the same issue anyway, as you are not using SSIS at all. A better thread would make more sense to start with, but this is a SSIS focused forum I'm afraid. I can branch into a new thread if you want.

1 - No issues with that method that would not be raised if using another execution host in the same location and context. Instead of running your application, try running DTSRUN in the same place, as the same user, and see what errors you get.

2 - To load a DTS package, you need DTS installed locally. If you use the AS Proc Task, then you need AS support and the AS task also installed locally.

|||

Thanks Darren,

Finally we decided that we will be processing the cubes using DTS and not SSIS, but I will surely try to test the stuff you mentioned , will let you know soon.

Thanks