Dynamically set Analysis Services Connection Manager in a Fooreach loop [SSIS 2008]

Question

Dynamically set Analysis Services Connection Manager in a Fooreach loop [SSIS 2008]

saadatbhatti

Old Hand

Points: 310
More actions
May 13, 2014 at 2:56 am

#285494

I am storing connection strings for "Analysis Services Connection Manager" in a database table and on run time i am fetching them all, storing in an object and iterating through them inside a Foreach loop. Variable gets updated with a new connection string in each iteration but somehow my "Analysis Services Processing Task" fails with "Invalid Connection String" Error.
Is there any way we can set set Analysis Services Connection Manager Dynamically. I can done this with OLEDB connections and that works. But somehow it's not working for Analysis Services Connection manager. Can anyone please share his/her experience ?
Many thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Can you post the exact error?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

saadatbhatti Old Hand Points: 310 More actions · Answer 2

The Exact Error message is as following

[Analysis Services Processing Task] Error: The connection string is not valid.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Can you post an example of such a connection string?

If you would copy paste them yourself in the connection manager and you run the package, does it work?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

saadatbhatti Old Hand Points: 310 More actions · Answer 4

Yes if i hard quote the connection string in Analysis Services Connection Manager it works. But when i tries to set it as expression it throws following error. I have googled it and found that many people are claiming it's not possible to set AS connection Dynamically. Is it True ?

[Analysis Services Processing Task] Error: The connection string is not valid.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

After some googling it seems indeed SSIS has an issue with dynamic SSAS connection (how stupid).

The alternatives are using AMO in a script task or using package configurations.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

saadatbhatti Old Hand Points: 310 More actions · Answer 6

What's AMO ? and How can package configuration can be used inside foreach loop? Do you think I should Update Package Configurations for Analysis Services Connection Manager in each Iteration ? Will it be possible

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Google won't kill you.

Anyway:

Developing with Analysis Management Objects (AMO)

An example:

Managing SQL Analysis Service Objects with SSIS and AMO[/url]

Regarding package configurations:

SSAS Connection String Dynamically[/url]

What you could do is create a child package that will hold the SSAS connection and do all of the work.

Create a parent package and inside that package loop over the different servers, update the configuration table first and then call the child package with an Execute SQL Task.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nm.rajesh SSC-Addicted Points: 421 More actions · Answer 8

Hi ,

I have even tried with Package configurations to update the connection string of a Analysis Services task , but it is not updating the connection string , please let me know if there is any work around.

Thanks