I am working on creating some auditing reports on our current SSIS framework. I would like to create a Reporting Services report that I can have a standard looking report and change the query based on the Package Name.
To do this I created a table that contains the Package name and the validation query. You need to make sure that each set of queries that you store has the same outputted column names and each Package Name should only have 1 record in the table. I have am using CTE to query multiple tables and then I am using one select statement at the end. For this example I am going to pull the Source and Destination row count.
INSERT INTO Audit.ValidationQueries ('PackageName','ValidationQuery') VALUES('Load Dim Product' ,'WITH DestRecordCount AS (SELECT COUNT(*) AS DestRecordCount FROM dbo.DimProduct) ,SourceRecordCount AS (SELECT COUNT(*) AS SourceRecordCount FROM AdventureWorks.Production.Product) SELECT DestRecordCount, SourceRecordCount FROM DestRecordCount CROSS JOIN SourceRecordCount' )
To build the report, I used the Select statement from my validation query to create a Data Set, named ValidationResults. I then built a Table off the 2 rows, DestRecordCount and SourceRecordCount. This part is pretty basic.
Now to set up the dynamic query. I created a new dataset that queries the Audit.ValidationQueries table. The report will now have one report parameter @PackageName.
SELECT ValidationQuery FROM Audit.ValidationQueries WHERE PackageName = @PackageName
Create a new parameter @ValidationQuery. Set the visibility to hidden. Set the Available Values to “Get values from a query”. Set the DataSet to ValidationResults and the value to ValidationQuery. This is going set the value of the parameter, @ValidationQuery, equal to the ValidationQuery stored in Audit.ValidationQueries.
I then go to my ValidationResults dataset that I created earlier. I change the query to an expression
That is it. When the report loads it will be looking for 1 parameter, @PackageName. The ValidationQuery DataSet will get the ValidationQuery and pass it along to the ValidationResults DataSet.