Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change SSRS Data Sources Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 12, Visits: 186
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
Post #1511894
Posted Thursday, November 7, 2013 7:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 612, Visits: 2,145
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.
Post #1512258
Posted Thursday, November 7, 2013 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 12, Visits: 186
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!

Post #1512260
Posted Thursday, November 7, 2013 7:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 612, Visits: 2,145
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.
Post #1512263
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse