March 8, 2014

SSRS Deployment–Complete Automation–2012 & 2008

 

One of the most demanding and long awaiting feature in SSRS is “Automation of Deployment”. So in this blog I am going to share simple steps to completely automate the deployment and will share the tested vb.net code, but before getting into the automated script let us explore the available options

  1. BIDS Development environment(or SQL data tools in 2012) can deploy your packages to the desired server
    1. Pros
      1. Very easy to configure
    2. Cons
      1. Not practical for production rollout, as most companies follows segregation of duties. So developer won’t have production access and DBA shouldn’t open the package in development tools.
      2. Most often you want to deploy only one or two changes in the specific report rather than the whole package
      3. Always deploy the latest version of code. We can’t specify the version or Branching in TFS/subversion.
  2. Report Manager
    1. pros
      1. Can be used by DBA with some knowledge in SSRS
      2. Can deploy specific objects
    2. Cons
      1. Completely manual and mistake can easily happen
      2. Some organisation don’t have dedicated DBAs and they don’t have much knowledge about the SSRS
      3. Deployment instruction will be very lengthy and will consume significant amount of developer’s time incase of multiple reports and shared dataset
  3. RS.exe ( via web service) – 2005 name space
    1. Pros
      1. Deployment can be  automated (partially)
      2. Famous RSscripter (was written by Jasper Smith) is developed based on 2005 namespace and was very helpful to produce automated scripts
    2. Cons
      1. The scripts are very tedious to edit and develop.
      2. There is no build tool available from Microsoft
      3. 2005 namespace doesn’t support linking of items(e.g. report to dataset and so on)
  4. RS.Exe (via web service) – 2010 Name space
    1. Pros
      1. Deployment can be fully automated include linking of items
    2. Cons
      1. No tools available in the market to automate the deployment
      2. RSscripter doesn’t get it’s update for 2010 namespace

So the best option is RS.exe with 2010 namespace. I was searching for the automated script and found the article from  John Desch in the Microsoft blog Click here

it is one of the best automated script available in the market now as it uses the latest namespace. But it has it own limitations so in the blog i have expanded the functionality and given the revised code and steps to automate your deployment.

Enhancement

    1. The code doesn't work with 2008Sp1 and above. It requires the buffer error fix.
    2. The code tries to link all the reports in the deployed report folder from the servers to their Data source. This will create a problem, if you want to deploy only two reports in the existing 8 report pack. So created an additional method (UpdateDataSources_report) to link only the deployed report into their respective sources
    3. The code Doesn't link the Shared data set to Report. so created an additional method (UpdateDataSet_report) which will automatically link your report to their respective datasets
    4. The existing code doesn't overwrite the files if they exist. changed the flags (for dataset and report only) as the deployment usually requires overwriting the existing objects.
    5. At last it would be easy if you have a single deployment script with command prompt variable so that you can pass to DBA. SO created a deploy.bat with variables

Automation Steps

  1. You can download the code and batch file from my skydrive (or one drive Smile)
    1. CommonScript.rss
    2. Deploy.bat
  2. Save both file in same folder in your local drive
  3. Create a folder named “Reports” under this new folder
  4. Now copy all the reports, datasource and dataset into the “Reports” folder
  5. Now Deploy.bat has the below variables amend them accordingly
  6. varServerPath=http://local/reportserver    ---- Desired Server URL
    set varReportFolder=Test/Testdeploy          ---- Server folder path --from the main
    Set varDatasetFolder=Datasets                   ---- Dataset folder path
    set varDataSourceFolder=Data Sources        ---- Datasource path
    Set varDataSourcePath=Data Sources          ---- Datasource path again (Yes this is Redundant)
    set varReportName=                                  -----If you want to restrict the script to one report then mention it’s name without

                                                                           extension else leave it blank
    set varReportFilePath=%\Reports                ---- Local folder path where you stored your reports and dependant objects.

 

Now, you can zip the folder and send it to DBA (Note: please test the script in your development server first)

 

FAQ

  • Can I refer two different datasource folder for different dataset?
    • NO, you need to declare a collection object and modify the code. This is very rare occasion.
  • Report is not able to link my dataset.
    • Check the name in your report. Shared dataset should be creaed with same name
  • Can I deploy report in multiple folder path
    • Yes, you need divide them into multiple deployment package

 

Revised Code (save the script as Commonscript.rss)

 

   1: 'Begin Script
   2:  
   3: Dim definition As [Byte]() = Nothing
   4:  
   5: Dim bytedefinition as [Byte]() = nothing
   6:  
   7: Dim warnings As Warning() = Nothing
   8:  
   9:  
  10:  
  11: 'Main Entry point of utility
  12:  
  13: Public Sub Main()
  14:  
  15: Console.WriteLine()
  16:  
  17: Console.WriteLine("Initiating Deployment")
  18:  
  19: rs.Credentials = System.Net.CredentialCache.DefaultCredentials
  20:  
  21: Try
  22:  
  23: 'Create the shared data source
  24:  
  25: CreateFolders(DataSourceFolder,"/","Data Sources","Visible")
  26:  
  27: 'Create the folder that will contain the shared data sets
  28:  
  29: CreateFolders(DataSetFolder, "/", "Data Set Folder", "Visible")
  30:  
  31: 'Create the folder that will contain the deployed reports
  32:  
  33: CreateFolders(ReportFolder, "/", "Report Folder","Visible")
  34:  
  35: Catch goof As Exception
  36:  
  37: Console.WriteLine(goof.Message)
  38:  
  39: End Try
  40:  
  41: ReadFiles(filepath, "*.rds")
  42:  
  43: ReadFiles(filepath, "*.rsd")
  44:  
  45: ReadFiles(filepath, "*.rdl")
  46:  
  47: 'Publish the report
  48:  
  49: 'PublishReport(ReportName)
  50:  
  51: 'UpdateDataSources(ReportFolder, DataSourcePath)
  52:  
  53: End Sub
  54:  
  55:  
  56:  
  57: 'Utility for creation of folders
  58:  
  59: Public Sub CreateFolders(ByVal folderName as string, ByVal parentPath as string, ByVal description as String, ByVal visible as string)
  60:  
  61: Console.WriteLine()
  62:  
  63: Console.WriteLine("Checking for Target Folders")
  64:  
  65: 'CatalogItem properties
  66:  
  67: Dim descriptionProp as new [Property]
  68:  
  69: descriptionProp.Name = "Description"
  70:  
  71: descriptionProp.Value= description
  72:  
  73: Dim visibleProp as new [Property]
  74:  
  75: visibleProp.Name = "Visible"
  76:  
  77: visibleProp.value= visible
  78:  
  79: Dim props(1) as [Property]
  80:  
  81: props(0) = descriptionProp
  82:  
  83: props(1) = visibleProp
  84:  
  85: Try
  86:  
  87: rs.CreateFolder(folderName,parentPath,props)
  88:  
  89: Console.WriteLine("Folder {0} successfully created", foldername)
  90:  
  91: Catch goof as SoapException
  92:  
  93: If goof.Message.Indexof("AlreadyExists")>0 Then
  94:  
  95: Console.WriteLine("Folder {0} already exists",foldername)
  96:  
  97: End If
  98:  
  99: End Try
 100:  
 101:  End Sub
 102:  
 103:  
 104:  
 105: 'Utility for reading files from the Report Sevices Project
 106:  
 107: Public sub ReadFiles(filepath as string, fileextension as string)
 108:  
 109: Console.WriteLine()
 110:  
 111: Console.WriteLine("Reading Files from Report Services Project")
 112:  
 113: Dim rptdirinfo As System.IO.DirectoryInfo
 114:  
 115: rptdirinfo = New System.IO.DirectoryInfo(filepath)
 116:  
 117: Dim filedoc As FileInfo()
 118:  
 119: filedoc = rptdirinfo.GetFiles(fileextension)
 120:  
 121: Try
 122:  
 123:     For rptcount As Integer = 0 To filedoc.Length-1
 124:  
 125:         If Not filedoc(rptcount).Name.ToString.Trim.ToUpper.Contains("BACKUP") Then
 126:  
 127:             SELECT Case fileextension
 128:  
 129:                 Case "*.rds"
 130:  
 131:                     CreateDataSource(filedoc(rptcount).tostring.trim)
 132:  
 133:                 Case "*.rsd"
 134:  
 135:                     CreateDataSet(filedoc(rptcount).tostring.trim)
 136:  
 137:                 Case "*.rdl"
 138:  
 139:                     PublishReport(filedoc(rptcount).tostring.trim)
 140:  
 141:             End Select
 142:  
 143:         End If
 144:  
 145:     Next
 146:  
 147: Catch goof as Exception
 148:  
 149:     Console.WriteLine("In ReadFiles " + goof.message)
 150:  
 151: End Try
 152:  
 153: End Sub
 154:  
 155:  
 156:  
 157: 'Utility for Creating Shared Data Sets contained in the project
 158:  
 159: Public Sub CreateDataSet(ByVal filename as string)
 160:  
 161: Dim valstart as integer
 162:  
 163: Dim valend as integer
 164:  
 165: Dim DSDefinitionStr as string
 166:  
 167: Dim DataSourceName as string
 168:  
 169: Dim QueryString as string
 170:  
 171: Try
 172:  
 173:     Dim stream As FileStream = File.OpenRead(filePath + "\" + filename )
 174:  
 175:     definition = New [Byte](stream.Length-1) {}
 176:  
 177:     stream.Read(definition, 0, CInt(stream.Length))
 178:  
 179:     stream.Close()
 180:  
 181:     For i As Integer = 0 To definition.Length - 1
 182:  
 183:         DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(definition(i).ToString)))
 184:  
 185:     Next
 186:  
 187:     valstart=DSDefinitionStr.ToString.Indexof("<DataSourceReference>")
 188:  
 189:     If valstart > 0 Then
 190:  
 191:         valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceReference>") + 21
 192:  
 193:         valend = DSDefinitionStr.ToString.IndexOf("</DataSourceReference>")
 194:  
 195:         DataSourceName=DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 196:  
 197:         Console.WriteLine(DataSourceName)
 198:  
 199:     End If
 200:  
 201:     Catch e As IOException
 202:  
 203:     Console.WriteLine(e.Message)
 204:  
 205: End Try
 206:  
 207: filename=filename.tostring.replace(".rsd","")
 208:  
 209: Console.WriteLine("Attempting to Deploy DataSet {0}", filename)
 210:  
 211: Try
 212:  
 213: Dim item as CatalogItem
 214:  
 215: item=rs.CreateCatalogItem("DataSet",filename, "/" + DataSetFolder, True, definition, nothing, warnings)
 216:  
 217: If Not (warnings Is Nothing) Then
 218:  
 219: Dim warning As Warning
 220:  
 221: For Each warning In warnings
 222:  
 223: if warning.message.tostring.tolower.contains("refers to the shared data source") then
 224:  
 225: Console.WriteLine("Connecting DataSet {0} to Data Source {1}",filename, DataSourceName)
 226:  
 227: Dim referenceData() as ItemReferenceData = rs.GetItemReferences("/" + DataSetFolder + "/" + filename,"DataSet")
 228:  
 229: Dim references(0) as ItemReference
 230:  
 231: Dim reference as New ItemReference()
 232:  
 233: Dim datasourceURL = DataSourcePath + "/" + DataSourceName
 234:  
 235: reference.name=referenceData(0).Name
 236:  
 237: Console.WriteLine("Reference name = " + reference.name)
 238:  
 239: reference.Reference=datasourceURL
 240:  
 241: references(0)=reference
 242:  
 243: rs.SetItemReferences("/" + DataSetFolder + "/" + filename, references)
 244:  
 245: else
 246:  
 247: Console.WriteLine(warning.Message)
 248:  
 249: end if
 250:  
 251: Next warning
 252:  
 253: Else
 254:  
 255: Console.WriteLine("DataSet: {0} published successfully with no warnings", filename)
 256:  
 257: End If
 258:  
 259: Catch goof as SoapException
 260:  
 261: If goof.Message.Indexof("AlreadyExists")>0 Then
 262:  
 263: Console.WriteLine("The DataSet {0} already exists",fileName.ToString)
 264:  
 265: Else
 266:  
 267: If goof.Message.IndexOf("published")=-1 Then
 268:  
 269: Console.Writeline(goof.Message)
 270:  
 271: End If
 272:  
 273: End If
 274:  
 275: End Try
 276:  
 277: 'UpdateDataSetSources(filename,DataSetFolder, DataSourceFolder,DataSourceName)
 278:  
 279: End Sub
 280:  
 281:  
 282:  
 283: 'Utility for creating Data Sources on the Server
 284:  
 285: Public Sub CreateDataSource(filename as string)
 286:  
 287: 'Define the data source definition.
 288:  
 289: Dim dsDefinition As New DataSourceDefinition()
 290:  
 291: Dim DataSourceName as string
 292:  
 293: Dim valstart As Integer
 294:  
 295: Dim valend As Integer
 296:  
 297: Dim ConnectionString As String
 298:  
 299: Dim Extension As String
 300:  
 301: Dim IntegratedSec As String
 302:  
 303: Dim DataSourceID As String
 304:  
 305: Dim PromptStr As String
 306:  
 307: PromptStr=""
 308:  
 309: Dim DSDefinitionStr As String
 310:  
 311: DSDefinitionStr = ""
 312:  
 313: DataSourceName=filename.tostring.trim.substring(0,filename.tostring.trim.length-4)
 314:  
 315: Console.WriteLine("Attempting to Deploy Data Source {0}", DataSourceName)
 316:  
 317: Try
 318:  
 319: Dim stream As FileStream = File.OpenRead(filepath + "\" + filename)
 320:  
 321: bytedefinition = New [Byte](stream.Length-1) {}
 322:  
 323: stream.Read(bytedefinition, 0, CInt(stream.Length))
 324:  
 325: stream.Close()
 326:  
 327: For i As Integer = 0 To bytedefinition.Length - 1
 328:  
 329: DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(bytedefinition(i).ToString)))
 330:  
 331: Next
 332:  
 333: Catch goof As IOException
 334:  
 335: Console.WriteLine(goof.Message)
 336:  
 337: End Try
 338:  
 339: If DSDefinitionStr.ToString.Contains("<ConnectString>") And DSDefinitionStr.ToString.Contains("</ConnectString>") Then
 340:  
 341: valstart = DSDefinitionStr.ToString.IndexOf("<ConnectString>") + 15
 342:  
 343: valend = DSDefinitionStr.ToString.IndexOf("</ConnectString>")
 344:  
 345: ConnectionString = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 346:  
 347: End If
 348:  
 349: If DSDefinitionStr.ToString.Contains("<Extension>") And DSDefinitionStr.ToString.Contains("</Extension>") Then
 350:  
 351: valstart = DSDefinitionStr.ToString.IndexOf("<Extension>") + 11
 352:  
 353: valend = DSDefinitionStr.ToString.IndexOf("</Extension>")
 354:  
 355: Extension = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 356:  
 357: End If
 358:  
 359: If DSDefinitionStr.ToString.Contains("<IntegratedSecurity>") And DSDefinitionStr.ToString.Contains("</IntegratedSecurity>") Then
 360:  
 361: valstart = DSDefinitionStr.ToString.IndexOf("<IntegratedSecurity>") + 20
 362:  
 363: valend = DSDefinitionStr.ToString.IndexOf("</IntegratedSecurity>")
 364:  
 365: IntegratedSec = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 366:  
 367: End If
 368:  
 369: If DSDefinitionStr.ToString.Contains("<DataSourceID>") And DSDefinitionStr.ToString.Contains("</DataSourceID>") Then
 370:  
 371: valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceID>") + 14
 372:  
 373: valend = DSDefinitionStr.ToString.IndexOf("</DataSourceID>")
 374:  
 375: DataSourceID = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 376:  
 377: End If
 378:  
 379: If DSDefinitionStr.ToString.Contains("<Prompt>") And DSDefinitionStr.ToString.Contains("</Prompt>") Then
 380:  
 381: valstart = DSDefinitionStr.ToString.IndexOf("<Prompt>") + 8
 382:  
 383: valend = DSDefinitionStr.ToString.IndexOf("</Prompt>")
 384:  
 385: PromptStr = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
 386:  
 387: End If
 388:  
 389: dsdefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
 390:  
 391: dsdefinition.ConnectString = ConnectionString
 392:  
 393: dsdefinition.Enabled = True
 394:  
 395: dsdefinition.EnabledSpecified = True
 396:  
 397: dsdefinition.Extension = extension
 398:  
 399: dsdefinition.ImpersonateUser = False
 400:  
 401: dsdefinition.ImpersonateUserSpecified = True
 402:  
 403: 'Use the default prompt string.
 404:  
 405: If PromptStr.ToString.Length=0 Then
 406:  
 407: dsdefinition.Prompt = Nothing
 408:  
 409: Else
 410:  
 411: dsdefinition.Prompt = PromptStr
 412:  
 413: End if
 414:  
 415: dsdefinition.WindowsCredentials = False
 416:  
 417: Try
 418:  
 419: rs.CreateDataSource(DataSourceName, "/" + DataSourceFolder, False, dsdefinition, Nothing)
 420:  
 421: Console.WriteLine("Data source {0} created successfully", DataSourceName.ToString)
 422:  
 423: Catch goof as SoapException
 424:  
 425: If goof.Message.Indexof("AlreadyExists")>0 Then
 426:  
 427: Console.WriteLine("The Data Source name {0} already exists",DataSourceName.ToString)
 428:  
 429: End If
 430:  
 431: End Try
 432:  
 433: End Sub
 434:  
 435:  
 436:  
 437: 'Utility to Publish the Reports
 438:  
 439: Public Sub PublishReport(ByVal reportName As String)
 440:  
 441: Try
 442:  
 443: Dim stream As FileStream = File.OpenRead(filePath + "\" + reportName )
 444:  
 445: definition = New [Byte](stream.Length-1) {}
 446:  
 447: stream.Read(definition, 0, CInt(stream.Length))
 448:  
 449: stream.Close()
 450:  
 451: Catch e As IOException
 452:  
 453: Console.WriteLine(e.Message)
 454:  
 455: End Try
 456:  
 457: reportname=reportname.tostring.replace(".rdl","")
 458:  
 459: Console.WriteLine("Attempting to Deploy Report Name {0}", reportname.tostring)
 460:  
 461: Dim item as CatalogItem
 462:  
 463:  
 464: Try
 465:  
 466:     item=rs.CreateCatalogItem("Report",reportname, "/" + ReportFolder, True, definition,nothing, warnings)
 467:  
 468:     'warnings = rs.CreateCatalogItem(reportName, "/" + ReportFolder, False, definition, Nothing)
 469:  
 470:     If Not (warnings Is Nothing) Then
 471:  
 472:         If item.Name <> "" then 
 473:  
 474:             Console.WriteLine("Report: {0} published successfully with warnings", reportName)
 475:             UpdateDataSources_report(reportName)
 476:             UpdateDataSet_report(reportName)
 477:         else
 478:     
 479:             Dim warning As Warning
 480:  
 481:             For Each warning In warnings
 482:  
 483:                 Console.WriteLine(warning.Message)
 484:  
 485:             Next warning
 486:             
 487:         end if
 488:  
 489:     Else
 490:  
 491:         Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
 492:         UpdateDataSources_report(reportName)
 493:         UpdateDataSet_report(reportName)
 494:     End If
 495:  
 496: Catch goof as SoapException
 497:  
 498: If goof.Message.Indexof("AlreadyExists")>0 Then
 499:  
 500: Console.WriteLine("The Report Name {0} already exists",reportName.ToString)
 501:  
 502: Else
 503:  
 504:     If  goof.Message.IndexOf("published")=-1 Then
 505:  
 506:     Console.WriteLine(goof.Message)
 507:  
 508:     End If
 509:  
 510: End If
 511:  
 512: End Try
 513:  
 514: End Sub
 515:  
 516:  
 517:  
 518: 'Utility to Update The Data Sources on the Server
 519:  
 520: Public Sub UpdateDataSources(ReportFolder as string, DataSourcePath as string)
 521:  
 522: rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 523:  
 524: Dim item as CatalogItem
 525:  
 526: Dim items as CatalogItem()
 527:  
 528: Try
 529:  
 530:  
 531:  
 532: items=rs.ListChildren("/" + ReportFolder, False)
 533:  
 534: For Each item in items
 535:  
 536: 'Console.WriteLine("          update date source called     --------"+ item.Path + " -----------")
 537:  
 538: If item.path.Indexof("rdl")>0 and ReportName = "" Then
 539:  
 540: 'Console.WriteLine("          update date source called     --------"+ item.path.Indexof("rdl").tostring() + " -----------")
 541:  
 542:     Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)
 543:  
 544:     For Each ds as DataSource in dataSources
 545:  
 546:         Dim sharedDs(0) as DataSource
 547:  
 548:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)
 549:  
 550:         rs.SetItemDataSources(item.Path, sharedDs)
 551:  
 552:         Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")
 553:  
 554:         'end if
 555:     Next
 556:  
 557: End IF
 558:  
 559: Next
 560:  
 561: if ReportName = "" Then
 562:  
 563:     Console.WriteLine("Shared data source reference set for reports in the {0} folder.", ReportFolder)
 564:  
 565: End if
 566:  
 567:  
 568: If ReportName <> "" then
 569:  
 570: '    Console.WriteLine("               " + "/" + ReportFolder + "/" + ReportName + "  -------------      second  update called        ---------------------- ")
 571:  
 572:     Dim dataSources() as DataSource = rs.GetItemDataSources( "/" +  ReportFolder + "/" + ReportName)
 573:     
 574:     For Each ds as DataSource in dataSources
 575:  
 576:         Dim sharedDs(0) as DataSource
 577:  
 578:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)
 579:  
 580:         rs.SetItemDataSources("/" +  ReportFolder + "/" + ReportName, sharedDs)
 581:  
 582:         Console.WriteLine("Set " & ds.Name & " datasource for " & "/" +  ReportFolder + "/" + ReportName & " report")
 583:  
 584:         'end if
 585:     Next
 586:  
 587:     Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolder + "/" + ReportName)
 588:     
 589: end if    
 590:  
 591: Catch goof As SoapException
 592:  
 593: Console.WriteLine(goof.Detail.InnerXml.ToString())
 594:  
 595: End Try
 596:  
 597: End Sub
 598:  
 599:  
 600:  
 601:  
 602: 'Utility to Update The Data Sources on the Server
 603:  
 604: Public Sub UpdateDataSources_report(ReportName as string)
 605:  
 606: rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 607:  
 608: Dim item as CatalogItem
 609:  
 610: Dim items as CatalogItem()
 611:  
 612: Try
 613:  
 614: 'If ReportName <> "" then
 615:  
 616: '    Console.WriteLine("               " + "/" + ReportFolder + "/" + ReportName + "  -------------      second  update called        ---------------------- ")
 617:  
 618:     Dim dataSources() as DataSource = rs.GetItemDataSources( "/" +  ReportFolder + "/" + ReportName)
 619:     
 620:     For Each ds as DataSource in dataSources
 621:  
 622:         Dim sharedDs(0) as DataSource
 623:  
 624:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)
 625:  
 626:         rs.SetItemDataSources("/" +  ReportFolder + "/" + ReportName, sharedDs)
 627:  
 628:         Console.WriteLine("Set " & ds.Name & " datasource for " & "/" +  ReportFolder + "/" + ReportName & " report")
 629:  
 630:         'end if
 631:     Next
 632:  
 633:     Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolder + "/" + ReportName)
 634:     
 635: 'end if    
 636:  
 637:  
 638: Catch goof As SoapException
 639:  
 640: Console.WriteLine(goof.Detail.InnerXml.ToString())
 641:  
 642: End Try
 643:  
 644: End Sub
 645:  
 646:  
 647:  
 648: 'Utility to link The Dataset with the Report
 649:  
 650: Public Sub UpdateDataSet_report(ReportName as string)
 651:  
 652: rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 653:  
 654:  
 655: Try
 656:  
 657:     Dim dataSets As ItemReferenceData() = rs.GetItemReferences("/" +  ReportFolder + "/" + ReportName, "DataSet")
 658:     
 659:     If dataSets IsNot Nothing AndAlso dataSets.Length > 0 AndAlso Not String.IsNullOrEmpty(dataSets(0).Name) Then
 660:     
 661:         For i as integer = 0 to dataSets.Length -1
 662:     
 663:             Dim references(0) as ItemReference
 664:             Dim sharedDataSet = New ItemReference() 
 665:             sharedDataSet.Name = dataSets(i).Name
 666:             Console.WriteLine("Attempting to Link Dataset {0}", dataSets(i).Name)
 667:             sharedDataSet.Reference = "/" + DataSetFolder + "/" + dataSets(i).Name 
 668:             references(0)=sharedDataSet
 669:             rs.SetItemReferences("/" + ReportFolder + "/" + ReportName, references)
 670:             Console.WriteLine("Report " + ReportName + " Linked to data set " + "/" + DataSetFolder + "/" + Convert.ToString(sharedDataSet.Name))
 671:         Next
 672:     
 673:     End If
 674:  
 675: Catch goof As SoapException
 676:  
 677:     Console.WriteLine(goof.Detail.InnerXml.ToString())
 678:  
 679: End Try
 680:  
 681: End Sub
 682:  
 683:  
 684:  
 685:  
 686: 'Function to Reference Data Sources
 687:  
 688: Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource
 689:  
 690:     Dim reference As New DataSourceReference()
 691:  
 692:     Dim ds As New DataSource
 693:  
 694:     reference.Reference = sharedDataSourcePath & "/" & dataSourceName
 695:  
 696:     ds.Item = CType(reference, DataSourceDefinitionOrReference)
 697:  
 698:     ds.Name = dataSourceName
 699:  
 700:     Console.WriteLine("Attempting to Link Data Source {0}", ds.Name)
 701:  
 702:     GetDataSource=ds
 703:  
 704: End Function



Happy reading Smile

28 comments:

  1. Fantastic work.

    ReplyDelete
  2. Great work, thank you for sharing.

    ReplyDelete
  3. Thank you Nishar for sharing, I have been looking for something like this for a while now.
    I had to make the following changes in order to make this work for me. This may have been due to my environment, (Reporting Services in Native mode vs. SharePoint mode), but thought I would share incase anyone else was having problems getting this to work for them.

    In the CommonScript.rss file, modify the following lines to ensure that the Data Sources and Datasets get linked to the reports correctly and without error.
    Line 233:
    Previous line: Dim datasourceURL = DataSourcePath + "/" + DataSourceName
    Modified line: Dim datasourceURL = "/" + DataSourcePath + "/" + DataSourceName

    Line 694:
    Previous line: reference.Reference = sharedDataSourcePath & "/" & dataSourceName
    Modified line: reference.Reference = "/" & sharedDataSourcePath & "/" & dataSourceName

    In the Deploy.bat file modify line 7 as follows.
    Previous: set varReportFilePath=\Reports
    Modified: set varReportFilePath=.\Reports

    ReplyDelete
    Replies
    1. @Keith : That is Interesting and Thanks for Sharing

      Delete
  4. @Keith F
    Thanks for the info. It works like a charm now.

    ReplyDelete
  5. I have a question, I installed SSRS on a virtual machine, and the above script works fine when deploying from the VM itself, however when I try a deployment from my host machine, I get an "http request fail with 401 unauthorized" error.
    Any ideas why??

    Thanks

    ReplyDelete
    Replies
    1. Are you using the Domain Account? if you are in same domain and have right access then you pretty much should get the access

      Delete
    2. That did it !!!
      I'm new to sql server/windows server so thank you so much for this valuable information.

      Delete
    3. Making some necessary changes in rsreportserver.config file in the tag will resolve the "http request fail with 401 unauthorized" error.

      Delete
    4. Authentication

      Delete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. Thanks a lot, a real great tool :)

    ReplyDelete
  8. @Nishar I found a limitation to the link dataset method when using shared datasets. The name of the shared dataset within a report file must be the same as the filename of the dataset, otherwise linking will fail.

    As far as I have searched, the only way to bypass this is to manually read the content of the .rdl file and search for references to shared datasets and get the actual name of the external file.

    Conclusion: if you are using this deployment script and your report files are using shared datasets, set their name to match the dataset file.

    ReplyDelete
    Replies
    1. Thanks dragos, Yes this is a limitation . please check the FAQ.

      Delete
    2. Sorry, long time since I read your post for the first time.

      As a sidenote, I have made some improvements to your script (add posibility to deploy image files and also create subfolders for the files, e.g. specify the reports folder as /Reports/Accounting/Office2). If you would like to take a look, I would gladly send it to you, maybe it would help others.

      All the best,
      Dragos

      Delete
    3. Dragos,

      Could you make your code available? I also need to deploy/publish image files along with RDL, Shared Datasources and Shared Datasets.

      Thanks.

      Delete
    4. Sure Rick,

      If you are interested send me and email to sebestin.dragos@gmail.com

      Delete
  9. My report is not mapping to relative datasouce after publish.
    UpdateDatasource method is failing.
    Can you please suggest?

    ReplyDelete
  10. Good Day Everyone,

    could anybody assist me in creating a Linked Report using a similar approach?

    Thank you for your help !

    ReplyDelete
  11. Nishar,

    Thank you for this script. It is working very well for me. I am wondering if you know of a way to delete all the reports/datasets but not the datasources prior to deploying the reports?
    I have been trying to do this through vbscript but for some reason I cannot use the FileSystemObject in my environment and the rs.deleteitem works for an individual report but I would like a way to delete all the *.rdl and *.rsd.

    Thanks again

    ReplyDelete
  12. I really appreciate your work and it is quite informative post thank you. SQL Server Integration Services

    ReplyDelete
  13. Appreciate your work on this. However, I am facing issue while deploying the SSRS report. I could login to the report server, but getting below error while the reports are getting copied. Please suggest what could be the problem.


    Initiating Deployment

    Checking for Target Folders
    The request failed with HTTP status 401: Unauthorized.

    Reading Files from Report Services Project
    Attempting to Deploy Data Source XYZABCDE
    In ReadFiles The request failed with HTTP status 401: Unauthorized.

    Regards,

    ReplyDelete
  14. Nishar,

    I want to create a Root Folder in Report Server, and under that I need to create various module folders. These module folders would have the reports, dataset and datasource folder.

    I am able to create the root level folder but when I try to create subfolder inside root folder "rs.CreateFolder" does not create a the same and gives error as "library!ReportServer_0-1!3778!10/26/2015-15:21:21:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidItemNameException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidItemNameException: The name of the item 'test/Reports' is not valid. The name must be less than 260 characters long. The name must not start with slash; other restrictions apply.;"

    Please suggest, how we can resolve this.

    ReplyDelete
  15. Quick question

    Is it possible to enter a SSRS username and password to use for the deploy rather than the local account?

    ReplyDelete
  16. You could add https://github.com/timabell/ssrs-powershell-deploy to your list

    ReplyDelete
  17. The code in UpdateDataSources_report assumes that each data source returned from GetItemDataSources is a Shared Datasource. This causes false errors when deploying a report with an embedded data source. My reading of the namespace says there is a way to test the items returned from GetItemDataSources for DataSourceDefinition vs. DataSourceReference. Only a DataSourceReference data source would be linked to an external data source file.

    I've tried to update your code to implement this test but have been unsuccessful. Is this something you can look at?

    ReplyDelete