SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it possible to alter the .XML in and RDL file via SQL script?


Is it possible to alter the .XML in and RDL file via SQL script?

Author
Message
DataGuy
DataGuy
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 238
Hi Guys,

We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via BIDS and report builder. The DW devlopers want to push a new version of the data warehouse to us in a few weeks and they have changed the naming conventions on roughly 50 columns in 30+ tables. As opposed to taking a reactive approach and waiting for the new wharehouse to go live and see which reports break, I'm wondering if it would be possible for me to execute a script against the report server to identify which reports have the columns that would be impacted by the change? In my previous role I've written scripts to convert the .xml in SSIS packackages into sting data and load it into table that I can query against , but SSRS is somewhat new to me and I'm still waiting for my permissions to be setup so I can execute queries.

Also, If I can query to identify the changes, is it possible to then write a SQL script to alter the .xml in the RDL files so we can avoid having to open each report and make the changes one at a time?

Lastly, are there any good tools out there that would allow us to do an impact analysis in an automated fashion as opposed to using SQL queries whenever schema changes occour?

Thanks in advance!
DataGuy
DataGuy
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 238
Bump
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2602 Visits: 2582
Code-1029433 (3/6/2013)
Hi Guys,

We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via BIDS and report builder. The DW devlopers want to push a new version of the data warehouse to us in a few weeks and they have changed the naming conventions on roughly 50 columns in 30+ tables. As opposed to taking a reactive approach and waiting for the new wharehouse to go live and see which reports break, I'm wondering if it would be possible for me to execute a script against the report server to identify which reports have the columns that would be impacted by the change? In my previous role I've written scripts to convert the .xml in SSIS packackages into sting data and load it into table that I can query against , but SSRS is somewhat new to me and I'm still waiting for my permissions to be setup so I can execute queries.

Also, If I can query to identify the changes, is it possible to then write a SQL script to alter the .xml in the RDL files so we can avoid having to open each report and make the changes one at a time?

Lastly, are there any good tools out there that would allow us to do an impact analysis in an automated fashion as opposed to using SQL queries whenever schema changes occour?

Thanks in advance!


First of all, I would pitch a screaming fit about the vendor changing column names in a data warehouse. The vendor should know what a major disruption this could cause (i.e., breaking EVERY query and report that refers to those columns). The vendor should do so only when there is a significant justification.

Second, RDL files are just like any other XML files. I'm not sure that you can work directly against the SSRS database as you may be thinking, though. You could pull the .rdl files of the deployed reports into a SQL Server table with SSIS as you have before and use XQuery (check Books Online for learning resources) to search and modify it. You probably should use the XML datatype for the column that holds the RDL, though, rather than a varchar or nvarchar. Once you have made the corrections, you could create new .rdl files and deploy them in place of the old ones.

As an alternative to having to change all your reports whenever the table definitions change, what about implementing an abstraction layer of views and coding the reports against these? When table definitions change, you need only update the view definitions accordingly rather than having to find and change all the references to the tables in the reports and queries. The reports and queries will never know the difference. In fact, this may be a reasonable request to make of your vendor - if it is going to be tinkering with the table definitions, provide and maintain the views that don't change.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
DataGuy
DataGuy
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 238
Thanks for the feedback wolfkill!
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