April 2, 2003 at 10:13 am
I posted this on the DTS list but will try it here as well...I have a DTS package that extracts, transforms and loads data nightly into a sql server 2000 datamart. This process has been working fine until a week or so ago when all of a sudden the filter for the incremental update [of the cube] task stopped being updated correctly. I have an activex task that queries the fact table for the record id range of the records just loaded then sets the incremental processing task filter. When I run this task manually from the DTS designer, it executes properly and sets the filter correctly. When the package is executed nightly as a job, the filter is not set properly. However, every other task in the package works just fine so I am inclined to not think it is a permissions [sql agent] issue. Any ideas?
Thank you,
Michael
Michael Weiss
April 4, 2003 at 2:51 pm
Michael -
Are there any other tasks in the package that update Analysis Services objects?
My question is based on this idea: the update you describe modifies a property value of the cube partition. Permissions (SQL Agent user to Analysis Services) must be in place for this process to succeed.
If other tasks in this package also update property values then permissions is not likely the problem. However, if this task alone attempts to modify AS then, perhaps, windows security is the culprit.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
April 4, 2003 at 3:52 pm
Thank you, Scot. No, there are no other tasks that modify an AS task in the package...the one in question is the only one. Do I need to make the SQLCmdAgent a member of the OLAP Administrators group? I thought I had it in there before but a quick check showed me I was mistaken or it was no longer a part of that group. I am sure the problem is permissions based...when I run the package manually it excecutes fine.
Thank you,
Michael Weiss
Michael Weiss
April 4, 2003 at 4:05 pm
Sounds like you're on the right track. Only OLAP Administrators can modify AS objects. Add the SQLCmdAgent user to the OLAP Administrators group and give it a try.
If that doesn't resolve the problem we'll dig a little deeper.
Good luck!
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
April 7, 2003 at 9:27 am
Thank you, Scot. I added the SQLCmdAgentExec to the OLAP Administrators group and also changed the user that the SQLCmdAgentExec logs in as to my user name (I am a member of the domain administrators group as well as a member of the OLAP Administrators group and the administrators group [on the server]). Still no luck...any ideas where or what I could check next?
Thank you,
Michael
Michael Weiss
Michael Weiss
April 7, 2003 at 9:35 am
Hmmmm....two thoughts come imediatly to mind:
1. Are SQL Server and Analysis Services on the same service patch? There are some very odd behaviors if the SP#s are out of synch.
2. Turn on logging for the package and let's see what error is being returned. Though the message likely will not give us a precise indication it should at least point us in the right direction.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
April 7, 2003 at 10:35 am
Both the SQL Server and AS are version 8.00.760 (SP3). I am running the job now with package logging turned on and will let you know what I come up with.
Thanks,
Michael
Michael Weiss
Michael Weiss
April 7, 2003 at 11:57 am
Okay, I ran the package as a job and it executed fine except the incremental filter is still not being set. When I open the package logs there are no errors listed and it shows each step as completing okay. At this point I am at a loss as to what to check next. Any suggestions?
Thank you,
Michael
Michael Weiss
Michael Weiss
April 7, 2003 at 12:06 pm
Could you save the package as a .dts (structured) file and email it to me? I'll see if I can put together a quick review/test session.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply