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
=Parameters!ValidationQuery.Value

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.