Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change SSRS Data Sources


Change SSRS Data Sources

Author
Message
alex.sqldba
alex.sqldba
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 701
Evening Guys,

I have about 150 Reports that use their own connection to a couple of data sources; these sources are set to change in the coming weeks.

Is there a quick and automated way of changing these data sources, ideally using Powershell.

So report1 currently looks at the MIS database on \\SERVERA

\\ServerA is changing to become \\SERVERB

I dont fancy doing all that opening, editing, and deploying.

Ideally I'd let a script loose to do it all...

Cheers
Alex
Steven.Howes
Steven.Howes
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 2328
I'm in the process of doing the same thing. I haven't found a way (though that doesn't mean there isn't one), what I've been doing is changing all the reports to use a shared datasource. Right now they point to the original DB, but when I go to switch I should just have to change the details of the shared datasource and that should fix all of them at once.

i tried to do this with 2005 but the shared datasources never worked once I deployed. We're on 2008 R2 and that problem has gone away so It's time to fix it the way I wanted to originally.
alex.sqldba
alex.sqldba
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 701
Totally Agree with that plan! Exactly the same situation here, they'll all end up using shared Data Sources; I just dont fancy changing each existing report manually!
Steven.Howes
Steven.Howes
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 2328
I'm still working on it... I don't even know how many reports I have, Over 400 i think. The bonus is, having to touch each report means I've been able to clean them all up, standardize the appearance, Standardize the query prompts, fix all the typos I made(I'm the DBA, report writer and QA). It does mean that It's taken me a lot longer than I had planned.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search