Monday, August 2, 2010

Export the SSRS Report Server's folder / item structure using TSQL

If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.

Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name

In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:

SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName

When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration

4 comments:

  1. add this one too:

    WHEN dbo.Catalog.Type = 8 THEN 'Data Set'

    ReplyDelete
  2. Longines Replica longines Italynon è il più antico tra gli orologiai svizzeri,replica longines 1832 orologi ma ha dimostrato che merita la sua statura. Fondata nel 1832, la sua eredità continua a essere considerata tra i marchi di orologi più affidabili, promettendo sia qualità nella fabbricazione che raffinatezza. Ciò è evidente nel La Grande Classique, fatto per uomini e donne. L'eleganza contenuta non è estranea al marchio, ma La Grande Classique offre questo con una custodia sottile.

    ReplyDelete
  3. Launched by bespoke shoemaker Jimmy Choo in 1996,jimmy choo for sale the brand follows in the repliche Flats footsteps of his statement: "the right shoes can change everything". From trainers to boots and dazzle-decked heels, the Jimmy Choo shoes edit has a style for every occassion.

    ReplyDelete
  4. Les répliques de chaussures Christian Louboutin sont très belles et très capricieuses.pas cher louboutin chaussures J'aime beaucoup cette réplique de chaussures Christian Louboutin. Le service en magasin est très bon, la qualité des chaussures est très bonne et le prix est très bon, je pense que je suis très bon marché, pas cher louboutin derby de bonne qualité et bon marché

    ReplyDelete