ProClarity: Is there any way we can use tables as sources of ProClarity views instead of cubes?

Is there any way we can use tables as sources of ProClarity views instead of cubes?

Hello! It is not possible. ProClarity is a SSAS-tool only.

You must build a cube in order to use ProClarity.

HTH

Thomas Ivarsson

sql

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing of cubes in a scheduled DTS

Hi everyone!

I created a DTS which does some data transformations before processing some cubes. It finished processing in abt 10mins when I run this DTS manually. However, when I schedule this DTS to run, it took around 3 over hours to run. Does anybody know where the problem lies? I have been looking for a solution for this for a long time and I'm hoping that somebody can help me...

Thank you!! :)

MichelleDo you have the latest SP on the server? I seem to recall something in sp3 that is supposed to help this|||My database is in a different server from the cube. The SQL server for this database is Server 2000, service pack 3a. The one for my cubes is version 7.00.623. Will the difference in version slow down the process?

Processing of cube created via DSO in AS 2005 fails

We are testing an application originaly written for AS 2000 with AS

2005.
This application uses DSO from VB to create and process cubes. I have

gotten
over the major hurdles of getting DSO to work with AS 2005. However,

upon
creating of the cubes, I get errors processing it (even from

Management
Studio). Here's the code (abridged to remove error handling) that

we use to
create a cube:

Dim dsoCube As DSO.MDStore
Set

dsoCube = m_dsoDatabase.MDStores.AddNew(cubeName)

' set the cube's

description
dsoCube.Description = sCubeDesc

' set the cube's

datasource
dsoCube.DataSources.Add

m_dsoDatabase.DataSources(mvarDBName)

Dim dboStr As String

dboStr = sLQuote & "dbo" & sRQuote & "."
' set the source

table (fact table) for the cube
dsoCube.SourceTable = dboStr &

sLQuote & FACT_TABLE & sRQuote

dsoCube.EstimatedRows =

164558
' specify access permissions to the cube by adding roles to the

cube
Dim dsoCubeRole As DSO.role
Set dsoCubeRole =

dsoCube.Roles.AddNew("BSA Role")
dsoCubeRole.SetPermissions "Access",

"RW"

' create cube's measures
'
Dim dsoMeasure As

DSO.Measure
Set dsoCube = m_dsoDatabase.MDStores.Item(cubeName)

Set dsoMeasure = dsoCube.Measures.AddNew("BaseAmt")

' set the

measure's source column, data type and the formatting

dsoMeasure.SourceColumn = dsoCube.SourceTable & "." &

_
sLQuote & "baseamt" & sRQuote

dsoMeasure.SourceColumnType = adDouble
dsoMeasure.FormatString =

"Currency"
' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggSum

Set dsoMeasure =

dsoCube.Measures.AddNew("Count")

' set the measure's source column,

data type and the formatting
dsoMeasure.SourceColumn =

dsoCube.SourceTable & "." & _
sLQuote

& "baseamt" & sRQuote
dsoMeasure.SourceColumnType =

adInteger
' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggCount
Set dsoMeasure =

dsoCube.Measures.AddNew("TranNo")

' set the measure's source column,

data type and the formatting
dsoMeasure.SourceColumn =

dsoCube.SourceTable & "." & _
sLQuote

& "TranNo" & sRQuote
dsoMeasure.SourceColumnType =

adInteger

' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggMax

' Create Calculated

members
Dim dsoCalculatedMember As DSO.Command
Set

dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgAmt")

' set the

command type
dsoCalculatedMember.CommandType = cmdCreateMember
'

set the MDX statement that defines the calculated member

dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgAmt] As

" & _
"'avg({LastPeriods(3 ,

[bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"

Set dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgCnt")
' set the

command type
dsoCalculatedMember.CommandType = cmdCreateMember

' set the MDX statement that defines the calculated member

dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgCnt] As

" & _
"'avg({LastPeriods(3 ,

[bookdate].&[2003].&[4].&[10])},
measures.[Count])'"

'

add the BookDate dimension
Dim dsoBookDateDim As DSO.Dimension
Set

dsoBookDateDim = dsoCube.Dimensions.AddNew("BookDate")

' add the

RecvPay dimension
Dim dsoRecvPayDim As DSO.Dimension
Set

dsoRecvPayDim = dsoCube.Dimensions.AddNew("RecvPay")

' get the list

of all tables used in this cube
' this list includes the fact table and

the dimension tables
' Note: Make sure that you do not repeat the same

table name twice.
dsoCube.FromClause = dsoCube.SourceTable & ", "

&
dsoBookDateDim.FromClause

dsoCube.joinClause =

dsoCube.joinClause & _
"(" & _
dboStr

& sLQuote & FACT_TABLE & sRQuote & "." & sLQuote

&
"bookdate" & sRQuote & _
" = " &

_
dboStr & sLQuote & "TblBookDate" & sRQuote &

"." & sLQuote &
"bookdate" & sRQuote & _

")"

dsoCube.SourceTableFilter = mvarFactTableFilter
' save the

cube definition in the metadata repository
dsoCube.Update

This

code runs fine, but during processing of the selected cube from
Management

Studio, the following error is displayed:
The Measures cube either does

not exist or has not been processed

There is no Measures cube, nor was

there ever. This does not happen when
processing cubes in AS 2000 or even in

AS 2005 if the cube was migrated from
AS 2000.

Can anybody help with

this?

Thanks in advance,
Boris Zakharin, MCAD
Metavante Risk and

Compliance

Hi Boris,

the problem is in the following line, which creates a calculated member. In AS2005 (as well as in AS2000) the member created in the cube scope should be prefixed with the cube name or CurrentCube, since Measures is the first name detected AS2005 treats it as a cube name. Pehaps AS2000 did not detected this error during the prosessing, but the calc member was disabled pr may be I'm wrong and CurrentCube was optional, but anyway, if you add CurrentCube to all calculated members it should start to process.

"Create Member Measures.[CustAvgAmt] As " & _
"'avg({LastPeriods(3 , [bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"

|||Thanks a lot, that worked

Processing jam-up

We have a number of jobs that process cubes when table loads complete. The last step in each of these jobs is to reprocess a "verification cube", which contains as linked measures the record counts from ALL other cubes. (We have a report which compares this against a direct SQL record count from the tables).

Anyway, we appear to have jammed up our Analysis Server (and had to restart) because one completing job issued a reprocess command for this "verification cube" while it (the verification cube) was still reprocessing in response to a previous job's completion.

OK, babbling aside, within a job I need to be able to ask "Is the verification cube currently processing, and if not, reprocess it".

Is this trivial, and I am not thinking correctly ? Any suggestions will be appreciated.

Which version of SQL Server 2000 or 2005 you are talking about?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

2005.

Just to mention . . . this has only happened once, and may just be one of those things . . . . But of course if we can better understand the problem and program to avoid it, that would be better.

|||

You should probably create a synchronization logic within your application and not let multiple jobs kick processing of the cube.

It is very hard to say whether processing of the cube is going on. The cube is not a single object it has multiple measure groups. Many processing commands, especially batch commands can include processing of any number of cube objects.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||Thanks Edward.

Processing Cubes via a SQL Server 2000 DTS

Hi,

I have a DTS which processes a set of AS 2000 cubes via a set of AS Processing Tasks.

I want to know how I can accurately trap and report errors once a cube fails to process?

Thanks in advance

Jon Derbyshire

The logging of processing errors in DTS ( SQL Server 2000 ) is bit limited.

You should try and look at migrating your application to SQL Server 2005 instead. I know it is bit a far fetched suggestion to migrate your whole solution to SQL Server 2005 just because of a single problem with accurately tracking processing errors. But the problem is: your will find increasingly harder to implement any new functionality with old version of the product. You will find it harder to find answers to your questions in the community that has moved to new ( different) technologies. At some point you should look at developing new solution using SQL Server 2005.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
Daniele

Unfortunatlely you cannot.
Your only option is to find a 32-bit workstation and run your DSO application from there. This is a restriction of AS2K. Sorry.

_-_-_ Dave

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
DanieleUnfortunately you cannot use AMO against Analysis Services 2000.
You can use DSO to remotely process Analysis Services object from another 32bit machine.
By the way you can use separate Redistributable component to install DSO
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Look for
"Microsoft SQL Server 2005 Backward Compatibility Components "

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Processing cube error

Hi,
I have a small problem when trying to process the cubes in AS. We have a
datawarehouse and have done couple of cubes also. In the beginning the cubes
processed fine, but after a while I have begin to get lots of errors. The
error message is "The attribute key cannot be foun. The record was skipped
because the attribute key was not found". Its a clear message, but the
problem is that the key it gives can be found and exists in the database...
Could there be other reasons for this message?
Any ideas? Thanks in advance!are you processing your dimensions?
"beatmasta" <beatmasta@.discussions.microsoft.com> wrote in message
news:2731F26C-2DB8-40D0-B791-A4478D9F0813@.microsoft.com...
> Hi,
> I have a small problem when trying to process the cubes in AS. We have a
> datawarehouse and have done couple of cubes also. In the beginning the
> cubes
> processed fine, but after a while I have begin to get lots of errors. The
> error message is "The attribute key cannot be foun. The record was skipped
> because the attribute key was not found". Its a clear message, but the
> problem is that the key it gives can be found and exists in the
> database...
> Could there be other reasons for this message?
> Any ideas? Thanks in advance!|||Nope...
And yes, that was the problem! Thanks a lot for helping the noobie!

Processing Cube

Hi,

Everyday I have a schedule job to processing the cubes, but I always receive the same error and then the Analysis Service will be stop. Here is the error msg from the Event Viewer:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (256)
Event ID: 22
Date: 9/18/2007
Time: 5:03:38 AM
User: N/A
Computer: HODB04
Description:
The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: File system error: The following error occurred during a file operation: The process cannot access the file because it is being used by another process. . (\\?\z:\OLAP\data\DM_POS_V2.0.db\POS DM.558.cub\Fact Sales.591.det\FACT SALES F2008 P2.7.prt\55.agg.flex.data)..

Thanks,

I would reccomend you to investigate the storage subsystem (RAID).

I have almost the same problem - the source of it was in the configurationof the RAID controller.

|||

What modifications did you made to the raid config to make it run? Whe are just running two mirrored disks on a HP so there's not much to modify.

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

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

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

Process Dimensions and Cubes Overnight

Hi All, any advice or help greatly appreciated, I need to Process Dimensions and Cubes Overnight, what is the best and most reliable way of achieving this.
Many Thanks in anticipation.
ShurlWhen you install Analysis Services, it installs a task in DTS to rubn analysis services processes. You can then schedule this DTS package to run at your convenience.|||Thanks for your help appreciated...

Cheers

Shurl