Change SSRS report embedded datasource to shared datasource

SSRS datasource change.sql

	use [ReportServer]

	DECLARE @Path nvarchar(250) = '/' --The path where you want to scan for reports
	DECLARE @Commit bit = 0

	DECLARE @SharedDSName nvarchar(250) = '/SharedDS' --The name of the shared dataset to use

	IF NOT EXISTS(SELECT 1 FROM [CataLog] Where [Path] = @SharedDSName)
	BEGIN
		print 'Could not find shared data source "' + @SharedDSName + '" !'
		RETURN
	END

	DECLARE @xmlTable TABLE (ReportPath NVARCHAR(max), ItemId UNIQUEIDENTIFIER, FixReport BIT, FixTable BIT, XmlData XML, XmlNS nvarchar(max))

	INSERT INTO @xmlTable (ReportPath, ItemId, XmlData, XmlNS, FixReport, FixTable)
	SELECT [Path], ItemId, XmlData, XmlNS, ReportNeedsFixing, TableNeedsFixing
	FROM (
		SELECT	[Path], 
				ItemId, 
				XmlData, 
				ReportNeedsFixing = IIF(XmlData.exist('/*:Report/*:DataSources/*:DataSource/*:ConnectionProperties') = 1, 1, 0),
				TableNeedsFixing = IIF(Extension IS NOT NULL, 1, 0),
				XmlNS = XmlData.value('namespace-uri((/*:Report)[1])','nvarchar(max)')
		FROM (
			SELECT	[Catalog].Path, 
					[Catalog].ItemID, 
					XMLData = CONVERT(XML,CONVERT(VARBINARY(MAX), [Catalog].Content)),
					DataSource.Extension
			FROM [Catalog]
				JOIN ReportServer.dbo.DataSource ON [Catalog].ItemID = DataSource.ItemID
			WHERE [Catalog].[Type] = 2 /*Report*/
		) RD
	) Reports
	WHERE (
		ReportNeedsFixing = 1 
		OR 
		TableNeedsFixing = 1
	)
	and Path like @Path + '%'

begin tran
	DECLARE @ReportPath NVARCHAR(MAX)
	DECLARE @ItemId UNIQUEIDENTIFIER
	DECLARE @XmlData XML
	DECLARE @XmlNS NVARCHAR(MAX)
	DECLARE @FixReport BIT
	DECLARE @FixTable BIT

	DECLARE xmlCursor CURSOR FOR
	SELECT ReportPath, ItemId, XmlData, XmlNs, FixReport, FixTable
	FROM @xmlTable

	OPEN xmlCursor
	FETCH NEXT FROM xmlCursor INTO @ReportPath, @ItemId, @XmlData, @XmlNS, @FixReport, @FixTable

	WHILE @@FETCH_STATUS = 0
	BEGIN
			IF @FixTable = 1
			BEGIN
					Print 'Setting Datasource for ' + @ReportPath

					UPDATE [dbo].[DataSource]  
					SET [Link] = (SELECT TOP 1 [ItemID] FROM [dbo].[Catalog] where [path] = @SharedDSName),
						Extension = NULL,
						CredentialRetrieval = 1,
						Prompt = NULL,
						ConnectionString = NULL,
						OriginalConnectionString = NULL,
						UserName = NULL,
						[Password] = NULL
					FROM DataSource
					JOIN [Catalog] ON DataSource.ItemID = [Catalog].ItemID
					WHERE [CataLog].ItemID = @ItemId
			END

			IF @FixReport = 1
			BEGIN
				Print 'Fixing Report for ' + @ReportPath

				SET @XmlData.modify('delete /*:Report/*:DataSources/*:DataSource/*:ConnectionProperties')
				SET @XmlData.modify('delete /*:Report/*:DataSources/*:DataSource/*:DataSourceReference')
				SET @XmlData.modify('replace value of (/*:Report/*:DataSources/*:DataSource/*:SecurityType/text())[1] with "DataBase"')

				IF @XmlNS = 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'
				BEGIN
					SET @XmlData.modify('
					declare namespace ns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition";
					declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
					insert <ns:DataSourceReference>{sql:variable("@SharedDSName")}</ns:DataSourceReference>
					before (/*:Report/*:DataSources/*:DataSource/*:SecurityType)[1]')
				END
				ELSE
				BEGIN
					SET @XmlData.modify('
					declare namespace ns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition";
					declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
					insert <ns:DataSourceReference>{sql:variable("@SharedDSName")}</ns:DataSourceReference>
					before (/*:Report/*:DataSources/*:DataSource/*:SecurityType)[1]')
				END

				-- Update the row with modified XML
				UPDATE @xmlTable
				SET XmlData = @XmlData
				WHERE ItemId = @ItemId
			END

		FETCH NEXT FROM xmlCursor INTO @ReportPath, @ItemId, @XmlData, @XmlNS, @FixReport, @FixTable
	END

	CLOSE xmlCursor
	DEALLOCATE xmlCursor

	SELECT * FROM @xmlTable order by ReportPath

	update [Catalog]
	SET Content = CONVERT(VARBINARY(MAX), x.XmlData)
	FROM @xmlTable x
	WHERE x.ItemId = Catalog.ItemID

IF @Commit = 0
	ROLLBACK TRAN
ELSE
	COMMIT TRAN