February 26, 2018 at 8:39 am
It's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 26, 2018 at 3:48 pm
Not sure if this is what you are looking for: https://www.softlanding.ca/about-Softlanding/resources/blog/getset-ssrs-data-sources-using-powershell
There is also this: https://www.mssqltips.com/sqlservertip/4738/powershell-commands-for-sql-server-reporting-services/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2018 at 1:13 am
sgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?
Why normal database backup can't work here? OR Is it quite difficult to recreate ?
February 27, 2018 at 12:27 pm
sgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?
It's really easy if you use the ReportingServicesTools module with Powershell - I think that module is the easiest way to get things out of SSRS. Using that, it's just:
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectory
Sue
February 27, 2018 at 12:44 pm
subramaniam.chandrasekar - Tuesday, February 27, 2018 1:13 AMsgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?Why normal database backup can't work here? OR Is it quite difficult to recreate ?
Don't have access to the backups, and as then you'd have to use a restore to get anything out, you'd end up right where you started. My objective is solely to populate a Shared Data Source that appears in the report RDL file, but as the project used to create the report is long gone, I have to recreate the project from scratch. As I had no response for long enough, I ended up just getting the password typed in by someone that knows it, after just creating the Shared Data Source within the new project, I had been hoping there was an easy way, and it now appears that Sue has a possible solution, although I'm not sure if I have the necessary tools installed, and if not, I won't be able to.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 27, 2018 at 12:50 pm
Sue_H - Tuesday, February 27, 2018 12:27 PMsgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?It's really easy if you use the ReportingServicesTools module with Powershell - I think that module is the easiest way to get things out of SSRS. Using that, it's just:
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectorySue
Can you provide just a bit more detail? I'm not sure if I have that module, and if not, I know I can't install it, but if I do have it, would it be a separate menu item somewhere in the start menu within Windows 10, or do I need to search for a particular executable? Or do I just try and run that command, substituting values as appropriate.,,,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 27, 2018 at 3:45 pm
sgmunson - Tuesday, February 27, 2018 12:50 PMSue_H - Tuesday, February 27, 2018 12:27 PMsgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?It's really easy if you use the ReportingServicesTools module with Powershell - I think that module is the easiest way to get things out of SSRS. Using that, it's just:
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectorySue
Can you provide just a bit more detail? I'm not sure if I have that module, and if not, I know I can't install it, but if I do have it, would it be a separate menu item somewhere in the start menu within Windows 10, or do I need to search for a particular executable? Or do I just try and run that command, substituting values as appropriate.,,,
No problem. Those kind of modules (as in not the Powershell standard ones) you need to download but it's not bad to get the MS ones.
In Powershell, just execute
Install-Module -Name ReportingServicesTools
It just hits the online Gallery with the modules and will download the latest.
Then when you want to use any of the cmdlets with that module, just start by loading the module. It's just
import-module ReportingServicesTools
It's the same to load any other modules. Once you have done that, you can execute that line I posted. If you want to see all of the commands available with that module, after doing the install and import you can just run this in powershell to see the commands for ReportingServicesTools:
Get-Command -Module ReportingServicesTools
Same with any other modules. I usually do them this way so I can get an easier to read list window that I can keep open:
Get-Command -Module ReportingServicesTools | Out-GridView
So you would just do this:
Install-Module -Name ReportingServicesTools
import-module ReportingServicesTools
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectory
In Powershell, if you want to see what modules are loaded, just run
get-module
If you want see what modules are available - others on the PC that you could import, just run
get-module -ListAvailable
Let me know if you need any other info, I'll pay better attention this time and follow up 🙂
Sue
February 28, 2018 at 9:37 am
Sue_H - Tuesday, February 27, 2018 3:45 PMsgmunson - Tuesday, February 27, 2018 12:50 PMSue_H - Tuesday, February 27, 2018 12:27 PMsgmunson - Monday, February 26, 2018 8:39 AMIt's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a shared data source as an RDS file, or somehow come up with the text necessary to create one from information on the data source in the report server. Any ideas?It's really easy if you use the ReportingServicesTools module with Powershell - I think that module is the easiest way to get things out of SSRS. Using that, it's just:
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectorySue
Can you provide just a bit more detail? I'm not sure if I have that module, and if not, I know I can't install it, but if I do have it, would it be a separate menu item somewhere in the start menu within Windows 10, or do I need to search for a particular executable? Or do I just try and run that command, substituting values as appropriate.,,,
No problem. Those kind of modules (as in not the Powershell standard ones) you need to download but it's not bad to get the MS ones.
In Powershell, just execute
Install-Module -Name ReportingServicesTools
It just hits the online Gallery with the modules and will download the latest.
Then when you want to use any of the cmdlets with that module, just start by loading the module. It's just
import-module ReportingServicesToolsIt's the same to load any other modules. Once you have done that, you can execute that line I posted. If you want to see all of the commands available with that module, after doing the install and import you can just run this in powershell to see the commands for ReportingServicesTools:
Get-Command -Module ReportingServicesToolsSame with any other modules. I usually do them this way so I can get an easier to read list window that I can keep open:
Get-Command -Module ReportingServicesTools | Out-GridViewSo you would just do this:
Install-Module -Name ReportingServicesTools
import-module ReportingServicesTools
Out-RsCatalogItem -ReportServerUri 'http://ServerName/ReportServer' -RsItem '/PathTo/DataSourceName' -Destination C:\TheDownloadDirectoryIn Powershell, if you want to see what modules are loaded, just run
get-moduleIf you want see what modules are available - others on the PC that you could import, just run
get-module -ListAvailableLet me know if you need any other info, I'll pay better attention this time and follow up 🙂
Sue
Sue,
Thanks so much. No worries on the delay. That execution of your PowerShell scripts successfully installed a pre-requisite (NuGet module), and then the needed module for SSRS, and then provided a .RSDS file as the output, but I'm not entirely sure whether I can now actually use that file within VS 2015. As I had actually gotten the shared data source set up within the project, I may not actually need this now, but it would be good to understand how, exactly to make use of the file, so that in the future, this problem can be easily solved more quickly. The XML format is not quite the same between what the .RSDS file has, and the .RDS file representing my created Shared Data Source.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2018 at 10:16 am
I downloaded this at some point, likely here, but I cannot remember who to give credit to! It needs to be run in the reporting server database.
WITH XMLNAMESPACES
-- XML namespace def must be the first in with clause.
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
,
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
,SDS
AS (
SELECT SDS.NAME AS SharedDsName
,SDS.[Path]
,CONVERT(XML, CONVERT(VARBINARY(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5
) -- 5 = Shared Datasource
SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM (
SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value('ConnectString[1]', 'varchar(150)') AS
ConnString
FROM SDS
CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN
)
) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
,CON.SharedDsName;
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 28, 2018 at 10:26 am
sgmunson - Wednesday, February 28, 2018 9:37 AMSue,Thanks so much. No worries on the delay. That execution of your PowerShell scripts successfully installed a pre-requisite (NuGet module), and then the needed module for SSRS, and then provided a .RSDS file as the output, but I'm not entirely sure whether I can now actually use that file within VS 2015. As I had actually gotten the shared data source set up within the project, I may not actually need this now, but it would be good to understand how, exactly to make use of the file, so that in the future, this problem can be easily solved more quickly. The XML format is not quite the same between what the .RSDS file has, and the .RDS file representing my created Shared Data Source.
I was just running it with the latest version - the format with the XML is not correct. It was working with one earlier version and seems broken again. It looks like it's still a bug - refer to this:
Out-RsCatalogItem not generating correct XML for data sources
So unfortunately, it won't work until they fix that. Reports seems fine, just not data sources.
You could shred the xml in the catalog table (content column) which has the connection strings. The only other alternative I can think of is using the web service, SOAP API. I have some script that grabs some SSRS items this way - I'll try to find it and post it, just for edification if nothing else.
Sue
February 28, 2018 at 12:05 pm
Sue_H - Wednesday, February 28, 2018 10:26 AMsgmunson - Wednesday, February 28, 2018 9:37 AMSue,Thanks so much. No worries on the delay. That execution of your PowerShell scripts successfully installed a pre-requisite (NuGet module), and then the needed module for SSRS, and then provided a .RSDS file as the output, but I'm not entirely sure whether I can now actually use that file within VS 2015. As I had actually gotten the shared data source set up within the project, I may not actually need this now, but it would be good to understand how, exactly to make use of the file, so that in the future, this problem can be easily solved more quickly. The XML format is not quite the same between what the .RSDS file has, and the .RDS file representing my created Shared Data Source.
I was just running it with the latest version - the format with the XML is not correct. It was working with one earlier version and seems broken again. It looks like it's still a bug - refer to this:
Out-RsCatalogItem not generating correct XML for data sourcesSo unfortunately, it won't work until they fix that. Reports seems fine, just not data sources.
You could shred the xml in the catalog table (content column) which has the connection strings. The only other alternative I can think of is using the web service, SOAP API. I have some script that grabs some SSRS items this way - I'll try to find it and post it, just for edification if nothing else.Sue
Okay ... cool enough beans, so to speak. It can wait.. Again, thanks so much for helping me get this far.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2018 at 3:43 pm
Try running the report , then click the report name as it runs in the top left path (Home > folder1> .... > reportName) and that will take you to the properties page for that report. You should see a download button there.
----------------------------------------------------
March 1, 2018 at 10:44 am
MMartin1 - Wednesday, February 28, 2018 3:43 PMTry running the report , then click the report name as it runs in the top left path (Home > folder1> .... > reportName) and that will take you to the properties page for that report. You should see a download button there.
That's for the RDL file only. Already did that. Thanks, though!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply