we have sql server 2000 database with some packages containing quite olap cubes. Cubes are in a separate server machine that has Analysis Services. But we have removed the cubes to another server and the cubes in the package point to the the cubes to the old server. Is there any quick method to change to the new server's cubes without to change them individually one by one?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
As far as I'm aware, no matter where you do it - at source (then backup/restore as abf) or destination you have to modify them individualy.
If you are talking about and "Analysis Services Processing Task", then yes, you could use Disconnected Edit to change the TreeKey value.

For example, the FoodMart 2000 cube installed by default could be:

SERVER01\FoodMart 2000\CubeFolder\Budget

Switching to your new server could then be

NEWSERVER01\FoodMart 2000\CubeFolder\Budget

You could even just write a quick ActiveXScriptTask in the package to do this.  Something like this would work:

'  Visual Basic ActiveX Script

Function Main()

	Dim objPackage, objTask, strTreeKey

	Set objPackage = DTSGlobalVariables.Parent

	For Each objTask In objPackage.Tasks
	   If objTask.CustomTaskID = "DTSOlapProcess.Task" Then
	      For each objOlapProperty In objTask.Properties
	         If objOlapProperty.name = "TreeKey" Then
	            objOlapProperty.value = Replace(objOlapProperty.value, "SERVER01","NEWSERVER01", 1, 1)
	         End If
	   End If

'Destroy Variables
	Set objOlapProperty = Nothing
	Set objTask = Nothing
	Set objPackage = Nothing

	Main = DTSTaskExecResult_Success

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
This would change all of them to the same thing, of course, but you could add in some fine tuning if need be.

Just execute that step, delete the ActiveXScript task, and save the package.
nonlinearlyAuthor Commented:
Very clear and handy solution....
thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.