Update Shared Datasource Path of all SSRS reports to different path

Question

I'm working on SSRS Report.

Recently I have changed my folder location of all SSRS report to different path. Also I need to change Datasource path. (As I have moved the reports again I need to set Datasource) So I can no it manually by going to Manage.

But is there any shortcut way to set Datasource path to all SSRS reports?

Solution 1

Finally, I found the solution and made my day.

I have wasted 5 hours of time to find the solution.

To update the shared Datasource path - Need to update Datasource table of Report Server.

SELECT cLog.Path,dSource.name
FROM   DataSource    AS dSource
       JOIN CATALOG  AS cLog ON  cLog.ItemID = dSource.ItemID
WHERE  dSource.flags = dSource.flags AND dSource.Link IS NULL AND dSource.ConnectionString IS NULL 
        AND dSource.NAME = 'NameofDatasource' AND cLog.path LIKE '%foldername%'
ORDER BY
       PATH

It will show all the reports which has no connection string. So now, you have to update with your [Link].

Note: To Get latest [Link] - You need to set up one of reports by manually setting the Datasource path and execute below query this will provide you latest [Link].

SELECT ds.Link
FROM   DataSource    AS ds
       JOIN CATALOG  AS c ON  c.ItemID = ds.ItemID
WHERE  ds.NAME = 'NameofDatasource' AND c.path LIKE '%foldername%' AND ds.Link IS NOT NULL

Now, only remains to update it with the same where clause. So it will update latest [Link] to all reports.

UPDATE dSource set [Flags] = [Flags] | 2, [Link] = 'PutLatestLink'
FROM   DataSource    AS dSource
       JOIN CATALOG  AS cLog ON  cLog.ItemID = dSource.ItemID
WHERE  dSource.flags = dSource.flags AND dSource.Link IS NULL AND dSource.ConnectionString IS NULL 
        AND dSource.NAME = 'NameofDatasource' AND cLog.path LIKE '%foldername%'

Note: Please be careful if you directly execute this query on live server. For the first time, Try to update only one particular report with above query and then do for others if it really needs and worked fine.

Solution 2

You can use the RS.exe or ReportSync utilities in order to move the reports and datasources.

Also you can update the Catalog table from ReportServer database using a T-SQL script.

Use this query to explore your datasources path.

SELECT
  a.Name,
  b.Path
FROM DataSource a
INNER JOIN Catalog b
  ON a.ItemID = b.ItemID

UPDATE the path for the datasources to point report path. Note it will work if the DataSource is not a shared datasource deployed to a specific folder.

UPDATE b
SET b.Path = '<your_path>'
FROM DataSource a
INNER JOIN Catalog b
  ON a.ItemID = b.ItemID
WHERE a.Name IN ('Datasource1', 'Datasource2')

I am not sure about this works since I cannot try it in this moment so I recommend you use the accepted migration tools RS.exe or ReportSync.

Let me know if this helps you.