SSRS datasource rename

  • Hello,

    I have about 100 reports that have shared datasource.

    How can I change my datasource name without having to go back to each report and point it to the new name once I make the changes.

    E.g: My datasource name is Dev, I want it to change it to 'SQLReports'

    Thank you.

  • I'm not aware of any way to do that. The existing reports aren't going to suddenly figure out what you want... they'll just fail because the data source is no longer there. Sorry...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Open SSRS and browse to the data source. Rename it. View dependent items and check a few to make sure it worked. If it didn't work, rename it back.

  • If that works, it will only be for reports stored within SQL Server. Any report rendered based on the .RPT file being present solely on the file system will not experience that benefit. Such reports are often included in web pages, and it's fairly common. So, as usual, the answer is likely, "it depends", and in this case, on where the existing reports function from.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • You could create a new datasource based on the old with only the name as the difference, and then point all the reports to the new one.

    First, create the data source on the Reports website. The rest is done in SSMS

    -- Step 1. Get the itemID of the current datasource

    select *

    from catalog

    where name like 'oldDataSourceName%'

    -- Step 2. Get the itemId of the new datasource

    select *

    from catalog

    where name like 'newDataSourceName%'

    -- Step 3. View all items linked to current data source (just to see how many rows you expect to update

    --and verify that there are actually reports using the data source)

    Select c.name, ds.itemid, ds.Link

    from datasource ds

    join catalog c on ds.itemid = c.itemid

    where link = 'Copy Item Id from step one here'

    -- Step 4. Update link to new datasource

    update datasource

    set link = 'Copy Item Id from step two here' -- new datasource ItemId

    where link = 'Copy Item Id from step one here' -- current datasource ItemId

    Test reports, then see if there are any items depending on the old datasource before deleting it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply