SQL2000 SP3 (DTS) has a custom task which allows for the
automation of incremental update of OLAP cubes. If this
is paired with a 'Dynamic Property' task on a 'fact'
table which has a key field which is a incremented txn
id - then you have a nice solution for scheduling a
regular drip feed to the cube ... or so I thought.

An MSDN KB article descript how these 2 features don't
work together (because the 'Analysis Task' is not 'free
threading'. This was reported in April 2003 - and is
still not addressed.

My wish list entry is to be able to modify the enhance
the ''Analysis Processing Task' so that a filter can be
assigned via a global variable rather than just a text
field typed at design time.

Execute DTS Package within enterprise manager, and get
error :

Need to run the object to perform this operation

Code execution exception: EXCEPTION_ACCESS_VIOLATION

This happens on a simple DTS package. The error occurs on
the first task (as Dynamic Properties Task). It tries to
assign the 'filter' property of an 'Analysis Processing
Task' (e.g. OLAP Analysis Services, incremental update of
a cube) from a Global variable (a string called 'filter').

However - If I execute this task - it works. It's just
when I execute the 'package' when I get the error.

In article <529501c376c7$351c4340$a501280a (AT) phx (DOT) gbl>, Nigel Smith
<nigel (AT) roundhouse-sw (DOT) com> writes
<snip>

Nigel,

The KB is a bit pants, and I will be rasing this with MS. I know what is
trying to be said and it is correct, but it really is confused.


What is should say is the because the OLAP Proc task is not free
threaded, then if you use a dynamic properties task, or any other task
such as an ActiveX Script Task (and they are the only two that I can
think of) that changes or interacts with the OLAP Proc task, then those
tasks must also be set to execute on the main package thread. The OLAP
Proc task is already set to execute on the main package thread by the
designer.

The same logic applies to an Execute Package Task where the child
package contains a non-free threaded task such as the OLAP Proc task.
The execute package task needs to be set to execute on the main thread
as well, otherwise you cannot ensure that the OLAP Proc task in the
child is also on the main thread.

Any free-threaded tasks that try and talk to a non-free threaded task
can raise an exception.

In summary, set your dynamic properties task to execute on the main
thread and it will work fine.

The main thread option can be found under the Workflow properties,
right-click the task.


There is an alias (sqlwish (AT) microsoft (DOT) com) which can be used to suggest
improvements for future releases of SQL Server. Real people, and they
are the relevant people, do read these mails so use it appropriately.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Many thanks - this has resolved the problem. I may e-mail Microsoft and
ask them to make the 'properties' of the workflow a little easier to
find !

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!