Reports migration form SCCM 2007 to SCCM 2012

Recenty I had some troubles during reports migration from SCCM 2007 to SCCM 2012.

By the way - I am going to write a quite big article about my struggling with SCCM upgrade - I hope the migration nightmare will ends this month :)

So as I was saying, after migrating reports I was received such an error:

Microsoft.Reporting.WinForms.ReportServerException
The report server cannot process the report or shared dataset. The shared data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)


Stack Trace:
   at Microsoft.Reporting.WinForms.ServerReportSoapProxy.OnSoapException(SoapException e)
   at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)
   at Microsoft.Reporting.WinForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
   at Microsoft.Reporting.WinForms.ServerReport.EnsureExecutionSession()
   at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)
   at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerWindowsForms.SetParameterValues_DoWork(Object sender, DoWorkEventArgs e)

I couldn't find what is the issue, I was doing it with the ReportSync tool I have read about here:

http://blog.coretech.dk/kea/migrate-reports-from-sccm-2007-to-sccm-2012-sp1/

But there was nothing about that error :/ so I was searching further and found that great article, it is quite similar buuut... it has explanation of how to get rid of thta error :)

http://www.mydreampage.net/

Blog is written as a one page so you have to search by "Migrate the SCCM 2007 reports to SCCM 2012" header.

In case the website was no longer available - you have to do the fallowing after migration:

1. Go to to website http://<your_sqlreporting_server>/reports
2. Go to report that you have problem with
3. Click on it - so you will have error on the screen
4. Click on the report name on the top of the page (for ex. if your path to the report on the top looks like "Home > ConfigMgr_WRO > Asset Intelligence > Hardware 01A - Summary of computers in a specific collection" <- click on the bolded name) - it will lead you to the properties of the report.
5. Change the datasource of the report - don't worry if the "test connecion" won't work.
6. Go to report and run it :) it should be all fine

Unfortunatelly I have had about hundreds of reports to correct the data source, figuring out the script was the priority :) I have found a great script on that website:

http://ask.sqlservercentral.com/questions/86369/change-datasource-of-ssrs-report-with-powershell.html?sort=oldest

For me it was like:

$newDataSourcePath = "/ConfigMgr_WRO/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}";
$newDataSourceName = "AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_";
$SiteCode = "WRO"
$serverName = "WROSCCM"
$reportFolder = "/ConfigMgr_WRO/ConfigMgr_OldSite";

$ssrs = New-WebServiceProxy -uri http://$serverName/ReportServer/ReportService2005.asmx?WSDL -UseDefaultCredential

$reports = $ssrs.ListChildren($reportFolder, $True)

$reports | ForEach-Object {
  
    $reportPath = $_.path

   $dataSources = $ssrs.GetItemDataSources($reportPath)
    $dataSources | ForEach-Object {
      
        $proxyNamespace = $_.GetType().Namespace
        $myDataSource = New-Object ("$proxyNamespace.DataSource")
        $myDataSource.Name = $newDataSourceName
        $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
        $myDataSource.Item.Reference = $newDataSourcePath
        $_.item = $myDataSource.Item
        $ssrs.SetItemDataSources($reportPath, $_)
        Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)";
        }

}


The script will modify all reports in the "$reportFolder" and it's subfolders! Isn't it great!?