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

7 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
  5. I have always had a good impression of Ray-Ban.buy ray bans wayfarer mens sale I just happened to buy it at this store. I chose this classic and I'm beautiful. After that,cheap Ray-Ban sunglasses I found that the price of the event is 50% cheaper than usual or other e-commerce. Very valuable

    ReplyDelete
  6. The store has been solving the problem for me and the service is very patient,cheap Soccer Jerseys this is a perfect shopping experience. When I received the jersey, I thought it was a good copy. I like this online store.cheap fc cincinnati Jerseys The clothes here are simple and elegant. Very satisfied with this purchase.

    ReplyDelete
  7. replica longines watches uk have been a landmark of the industry since their brand began in 1832 in Saint-Imier. Today, replica mens longines 1832 watches the brand offers a wide variety of watches for men and women. Watches manufactured by Longines have been utilized throughout history for everything from exploration ventures to the expression of elegance. In addition to being famous for their ties to the racing and equestrian worlds, Longines watches are globally recognized for their timeless chic sophistication. we have a variety of Longines watches for sale, all new, beautiful, and guaranteed to be genuine. Even better, when shopping our Longines watches online, you know you are getting the best value. Browse our selection to find the Internet’s best Longines watch price.

    ReplyDelete