Let's start from beginning, what I have are
The old SQL Server is at 2008R2 version, reporting service is installed by following default setting
- Reporting Service URL: http://<SS2008R2>/reportserver
- Report Catalog DB name: ReportServer
The new SQL Server is 2012, reporting service is installed by following default setting
- Reporting Service URL: http://<SS2012>/reportserver
- Report Catalog DB name: ReportServer
What I need to do is to migrate hundreds of reports from SS2008R2 to SS2012, and update all connection strings from SS2008R2 to SS2012.
There were quite a lot ways to do this task, RS utility, report migration tool published by Microsoft (http://www.microsoft.com/en-au/download/details.aspx?id=29560). But because connection string inside the reports needs to be updated as well, I finally decided to write a short C# codes to do the migration. What the code will do is
- copy the reports from SS2008R2
- deploy the reports to SS2012
- update the data source to SS2012
The code is quite simple, firstly ReportingService2010 web services from both SS2008R2 and SS2012 need to be added. usually it is at http://<ReportServer>/reportserver/reportservice2010.asmx
Now I initialized both source server and target server
RSSource2010 = new RSSource.ReportingService2010();
RSSource2010.Credentials = System.Net.CredentialCache.DefaultCredentials;
RSSource2010.Url = @"http://" + SourceServer + "/reportserver/reportservice2010.asmx";
RSTarget2010 = new RSTarget.ReportingService2010();
RSTarget2010.Credentials = System.Net.CredentialCache.DefaultCredentials;
RSTarget2010.Url = @"http://" + TargetServer + "/reportserver/reportservice2010.asmx";
Now below code shows how to migrate the report and change the data source connection string. I have made it a bit simpler. In a production enviornment it could be far more complicated
//to make it simple, assume the report will be migrated to the root folder, "/"
string TargetPath = "/";
//get source report, here the variable ReportPath is the source report
//we can get it from catalog table (not suggested by MS) or from the webservice
byte[] ReportContents = RSSource2010.GetItemDefinition(ReportPath);
RS2010_Target.Warning[] warnings = null;
try
{
//create the target item
RS2010_Target.CatalogItem TargetItem = RSTarget2010.CreateCatalogItem("Report", "Migrated_Report", TargetPath, true, ReportContents, null, out warnings);
//need to change the data source of the target item to new server
//to make it simple, assume the data source connection string is embeded in the report
//otherwise it could be an expression or reference data source
RS2010_Target.DataSource[] DSs = RSTarget2010.GetItemDataSources(TargetItem.Path);
RS2010_Target.DataSource[] NewDSs = new RSTarget.DataSource[DSs.Count()];
for (int i = 0; i < DSs.Count(); i++)
{
//copy new data source name
RS2010_Target.DataSource NewDS = new RSTarget.DataSource();
NewDS.Name = DSs[i].Name;
//read source report data source
var t = DSs[i].Item.GetType();
if (t.Name == "DataSourceDefinition")
{
RS2010_Target.DataSourceDefinition TargetDSDef = new RSTarget.DataSourceDefinition();
RS2010_Target.DataSourceDefinition SourceDSDef = (RS2010_Target.DataSourceDefinition)DSs[i].Item;
//set target report data source connection string to new server
//set target report data source properties by copying from source
TargetDSDef.ConnectString = ((RS2010_Target.DataSourceDefinition)DSs[i].Item).ConnectString.ToUpper().Replace(SourceServer.ToUpper(), TargetServer.ToUpper());
TargetDSDef.Enabled = true;
TargetDSDef.Extension = ((RS2010_Target.DataSourceDefinition)DSs[i].Item).Extension;
TargetDSDef.CredentialRetrieval = RSTarget.CredentialRetrievalEnum.Integrated;
TargetDSDef.WindowsCredentials = ((RS2010_Target.DataSourceDefinition)DSs[i].Item).WindowsCredentials;
NewDS.Item = (RS2010_Target.DataSourceDefinitionOrReference)TargetDSDef;
NewDSs[i] = NewDS;
}
}
//set the data source
RSTarget2010.SetItemDataSources(TargetItem.Path, NewDSs);
}
The code works. If you open the report, you can find the report runs correctly, data is coming from SS2012, etc.
BUT
If you open the report via Report Builder, the connection string has been updated. The connection string from report builder will be
DATA SOURCE=<RS2012>;INITIAL CATALOG=REPORTSERVER
However if you query Catalog table by using below statement, You can see the data source coming from Catalog table is still pointing to RS2008R2
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT ReportName = name
,DataSourceName = x.value('(@Name)[1]', 'VARCHAR(250)')
,DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null and c.Name like '%the report migrated%'
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
ORDER BY name ;
I tried this procedure on my VM machine and got the identical result, so I am not quite sure what caused this discrepancy though the report after migration works. If you are accidently reading this thread, and know what happened, please let me know :D
