Hi
An update on that : I looked for further workarounds. Unfortunately, I still wasn't able to reliably set the username & password on the datasource. It looks to be possible using web service calls (see
http://stackoverflow.com/questions/1454 ... erver-side for example). However those methods rely on specific SSRS versions and server configurations (IIS settings, etc.) and we can't allow ourselves to rely on that. So we'll continue to manually set the datasource credentials for now. Although we could at least make an SQL query to check if the datasource already exists and set the datasource's deployment condition accordingly (fix for my question #3).
But I do have good news! I was able to fix the reports' datasource link even if they are not in the same folder. Here is my step-by-step :
- In Advanced Installer, make sure you have the following install parameters : Datasource Folder, Default Datasource Name, Install Start Time, Report folder.
- In Advanced Installer, add an SQL query with only SELECT GETDATE() and assign it to the Install Start Time parameter. Then put the custom action at the very start of the sequence (before Wizard.Searches).
- Deploy datasources & reports as usual.
- In Advanced Installer, create an SQL Query with the script below. Also, add the following replacements : Install Start Time, Datasource Folder, Default Datasource Name (see the first lines of the script). Then put the custom action at the very end, in the Finish Dialogs section. This is the script that will fix all the broken datasource links for the reports we just deployed.
Code: Select all
DECLARE @StartTime DATETIME,
@DatasourceFolder VARCHAR(425),
@DatasourceFolderItemId UNIQUEIDENTIFIER,
@DefaultDatasourceName VARCHAR(425)
SET @StartTime = '!!!INSTALLSTARTTIME!!!'
SET @DatasourceFolder = '/!!!DATASOURCEFOLDER!!!'
SET @DefaultDatasourceName = '!!!DEFAULTDATASOURCENAME!!!'
DECLARE @Reports TABLE
(
ReportItemId UNIQUEIDENTIFIER,
ReportPath VARCHAR(425),
ReportName VARCHAR(425),
DatasourceId UNIQUEIDENTIFIER,
DatasourceName VARCHAR(425)
)
SET @DatasourceFolderItemId = (
SELECT ItemId
FROM dbo.[Catalog]
WHERE [Path] = @DatasourceFolder
AND [Type] = 1 -- Folder
)
INSERT
INTO @Reports
(
ReportItemId ,
ReportPath ,
ReportName ,
DatasourceId ,
DatasourceName
)
SELECT c.ItemID,
c.[Path],
c.Name,
ds.DSID,
ds.Name
FROM dbo.[Catalog] c
JOIN dbo.DataSource ds ON ds.ItemID = c.ItemID
WHERE ds.Link IS NULL
AND c.[Type] = 2 -- Report
AND (
c.ModifiedDate >= @StartTime
OR
c.CreationDate >= @StartTime
)
--SELECT * FROM @Reports
UPDATE ds
SET ds.Link = ISNULL(c1.ItemID, c2.ItemID)
FROM @Reports r
JOIN dbo.DataSource ds ON ds.ItemID = r.ReportItemId
AND ds.DSID = r.DatasourceId
LEFT
JOIN dbo.[Catalog] c1 ON c1.Name = ds.Name
AND c1.ParentID = @DatasourceFolderItemId
LEFT
JOIN dbo.[Catalog] c2 ON c2.Name = @DefaultDatasourceName
AND c2.ParentID = @DatasourceFolderItemId
-- This select is needed because Advanced Installer is expecting a return column.
SELECT ReportItemId
FROM @Reports
ORDER
BY ReportName ASC
Quick recap:
#1 : Cannot use different windows credentials than mine when deploying. No solution yet, this is on the TODO list.
#2 : Deploying using a folder hierarchy. The workaround is to create multiple deployment items.
#3 : Deploying a datasource automatically overwrites it if it exists. This is a "maybe TODO". But I have a possible workaround via SQL query and putting a condition on the deployment.
#4 : Reports cannot find the datasource. This is an Advanced Installer problem. See my workaround above.
#5 : Sometimes empty folders get deployed. This is on the TODO list. There might be a way to create an SQL script to delete it at the end of the installation.
#6 : Cannot set the datasource credentials, it defaults back to Windows Authentication. This is an Advanced Installer bug and should be fixed. There might be some way to do it programmatically (see above) but it seems unreliable. In the meantime, I will have to manually set the credentials after installation.
