Using a parameterized SSIS package as a data source for SQL Reporting Services

As you may know, SSRS reports can use Integration Services packages as a source of data. This technique opens up some interesting possibilities around pre-processing data before it is rendered in a report.

I’m going to take this one step further and describe how to pass the package parameter values selected by the user in Reporting Services. These values are passed to the package so that it can do additional work like filter the resultset it returns to SSRS. You could also use this technique to send instructions to control of flow logic you have inside the package, etc.

The Basics:

First, if you haven’t played with SSIS as a report server data source, read these:

https://msdn2.microsoft.com/en-us/library/ms159215.aspx

https://msdn2.microsoft.com/en-us/library/ms156302.aspx

https://msdn2.microsoft.com/en-us/library/ms345250.aspx

After you have mastered the basics, follow the steps below…

Create an SSIS Package:

1. Create a new SSIS package, right-click the Control Flow tab, and choose Variables from the context menu.

 

2. Add a new variable: Name it filterValue, give it a Data Type of Int16 and a default value of 0.

 

3. Insert a single Data Flow Task into your control flow.

 

4. Drop an OLE DB Source in the data flow and configure it to connect to the AdventureWorks database.

 

5. Choose a Data access mode of SQL Command.

 

6. Next, we’re going to add a command which is parameterized. Enter or paste this text in the SQL Command text box:

SELECT EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours,SickLeaveHours, CurrentFlag, rowguid, ModifiedDate
FROM HumanResources.Employee
WHERE (EmployeeID = ?)

 

7. Click the Parameters button. Add a Parameter Mapping of Parameter = EmployeeID, Variables = User::filterValue. Close the Set Query Parameters dialog, and you’ve basically created a simple SELECT which is dynamically filtered by whatever value lives in the filterValue variable of your package. (Note: Clicking Preview won’t work right now – no worries!)

 

8. Add a Data Reader Destination to your data flow and connect the OLE DB source to the Data Reader Destination.

 

9. Double-click the DataReaderDest you just dropped in the flow and choose the Input Columns tab. Select all the available input columns, and click OK.

 

10. From the Debug menu, choose Start Debugging to run your package. We won’t see anything especially interesting happen at this point, but we shouldn’t see any errors. Remember to Stop Debugging, then save and close your package.

 

11. I’d go ahead and copy the resulting *.dtsx to somewhere easy to get to, like the root of C:\

Create the report:

  1. Create a new Report Server Project, right click the Reports folder in Solution Explorer, and add a New Item…In this case we’ll be creating a report.
  2. Add a new Parameter to this report. Name it filterValue, set its data type as Integer, give it available values of 0, 1, 2, and 3, and finally set the default value of this parameter to 0.
  3. Create a new Dataset in this report, and make sure to choose SSIS in the Type combo-box.
  4. The magic happens in the Connection String box. Remember, that in SSRS 2005 we can actually build the connection string out of an expression. Our expression will call the SSIS package and dynamically change the value we pass in to the filterValue variable in the SSIS package:

= "/file c:\package.dtsx /Set \Package.Variables[filterValue];" & Parameters!filterValue.Value

5. Click OK. In the Query String box of the Dataset dialog, enter the name of the Data Reader Destination object in the SSIS package (probably “DataReaderDest”) .

6. That’s pretty much it. Go ahead and create the report as you normally would from the data that comes back. When you run the report you’ll be able to change the value for the filterList parameter, which is turn is passed to the SSIS package, and causes SQL to send you a filtered resultset via the Data Reader in the package.