Bagikan melalui

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

  • Artikel

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.

Comments

  • Anonymous
    October 19, 2006
    The comment has been removed

  • Anonymous
    October 19, 2006
    I solved the problem that I posted. Basically, I had to surround the date parameter within quotes.

  • Anonymous
    November 27, 2006
    Hi,I am unable to find the SSIS data source type.Does anyone know why this could happen?thanks

  • Anonymous
    November 29, 2006
    Hi Sameer - The SSIS extension is turned off by default. You need to go into your rsreportserver.config file and uncomment the lines which refer to it. More information on this is in books online

  • Anonymous
    December 04, 2006
    Hi Russel,To create report, we need to have a dataset created. In my case, I am not able to see any fields on the dataset. How do I get to see them?Thanks,S Suresh

  • Anonymous
    December 05, 2006
    Hi,I don't have Report Server on my machine.I have succesfully created the SSIS package. I just want to call the SSIS package and populate the value in the PREVIEW tab of the  SSRS report. Is it possible?TIA,Pradeep

  • Anonymous
    December 09, 2006
    Hi Suresh --I assume that you are asking this question in relation to using an SSIS package as your data source? In that case, you just need to make sure you put a DataReader in the SSIS package. If you are asking a more general question about fields not showing up, after building your query, click the refresh button on your toolbar.

  • Anonymous
    December 09, 2006
    The comment has been removed

  • Anonymous
    December 13, 2006
    FYI: your instructions say to create a report parameter call filterParameter, but in the connection string you use filterValue

  • Anonymous
    December 13, 2006
    Good catch! Thanks.

  • Anonymous
    February 22, 2007
    The comment has been removed

  • Anonymous
    March 21, 2007
    The comment has been removed

  • Anonymous
    March 28, 2007
    You cannot use SSIS packages as the source of a report model: We only support SQL, SSAS, and Oracle (in SP2)

  • Anonymous
    March 30, 2007
    The comment has been removed

  • Anonymous
    June 05, 2007
    The comment has been removed

  • Anonymous
    June 06, 2007
    I'm not 100% sure I understand your question, but SSRS and SSIS must be on the same machine if you want to use an SSIS data source. Did this help?

  • Anonymous
    June 06, 2007
    Sorry - did not state the question well. SSRS and SSIS are on the same machine.  Report using SSIS source runs fine in Visual Studio. Same report deployed to the web server (on the same machine) and run in Internet Explorer fails to run stating a variety of login failures (have tried like 10 different seemingly valid login combos).  Hope I explained it better and thanks for trying to help out.

  • Anonymous
    June 08, 2007
    Ah, OK. you're running into an issue where the connection managers in the SSIS package aren't picking up the identity of the user running the report, etc...There is no way around this. Try hard-coding credentials in the SSIS connection managers, and your problem should go away.

  • Anonymous
    June 13, 2007
    Out of town for a while and turning attention back to this.  Could you please provide a sample of how to "hard-code the credentials in the SSIS connection manager?" Thanks.

  • Anonymous
    June 13, 2007
    Actually figured out where to code the credentials in the .ds file and still got the same error message.  Thanks for trying to help out on this.

  • Anonymous
    August 07, 2007
    Hi, I have a similar issue in setting up the SSRS with the SSIS data source.  It is working very well in Visual Studio.  But when the report deployed to Report Manager, it doesn't work.  Error Msg: 'The package failed to validate'I tried so many different combinations:(1) SSIS package: use SQL authentication (with hard-code sql server user credentials)(2) SSIS package: use Window AuthenticationIn Report Manager:Selected 'Credentials stored securely in the report server' and checked 'Use as Windows credentials when connecting to the data source'OR 'Windows integrated security'OR 'Credentials are not required'Tried many ways still not working... Any suggestions is very helpful.Thanks so much ...

  • Anonymous
    August 24, 2007
    The comment has been removed

  • Anonymous
    September 03, 2007
    Hi Dmurphy, I think the quotation marks are simply not at the right position in your connection string. It should read something like this="/file C:WWCI-BTI_Financials.dtsx /SetPackage.Variables[StartDate];" & Parameters!StartDate.ValueIf the filename of the package contains blank you have to use double quotation marks inside the string like this="/file ""C:WWCI-BTI_Financials.dtsx"" /SetPackage.Variables[StartDate];" & Parameters!StartDate.ValueHope this helps.

  • Anonymous
    October 23, 2007
    My connection string is as follows:="/file d:AFELogSouthCopy.dtsx /Set AFELogSouthCopy.Variables[AFE_Report_ID];" & Parameters!AFE_Report_ID.ValueWhen I try to run the report I get the error "Cannot crate a connection to data source 'SSISNonShared'.  I have double checked and that is the data source name.If I just use -f D:AFELogSouthCopy.dtsx it connects fine.Any ideas??

  • Anonymous
    October 29, 2007
    Okay.  I figured out my problem with the connection.  Everything works now.Now I get the same thing that the above poster, Wendy, is receiving when I deploy to report server. Error Msg: 'The package failed to validate'Any ideas?  Is anyone reading these anyway??

  • Anonymous
    October 31, 2007
    OK, now let's talk about deploying the SSIS and RS objects. The connection string or the rds currently points to a mapped drive. I want to deploy the SSIS package in different environments so I need to also establish a dtsConfig file.How do I associate the SSIS for the report to a dtsConfig file? I assume that once I deploy the data source I can update the connection string on the report server to point to the UNC for the DTS package, but how do I pass different parameters via config (SQL Server property for the OLE DB source, for example) so that each environment references the appropriate server?

  • Anonymous
    November 05, 2007
    Not sure I follow you...

  • Anonymous
    November 25, 2007
    HI ,I am doing the same Steps as mentioned above but still I am not able to connect to SSIS package.Steps that I Followed are as:Modification in RSReportDesigner file.Modification in rsreportserver.Config file Creating SSIS package with same variables. Executing Package. Creating the dataset. Selecting Type as SSIS and mentioning below mentioned path as a connection String = "/file c:package.dtsx /Set Package.Variables[filterValue];" & Parameters!filterValue.ValueMentinoning DataReaderDestination name as DataReaderDest in the QueryString.Type as TextStill the Error is coming ;Connection can not be made to the Datasource..Can anyone help me out to resolve the issue?

  • Anonymous
    February 12, 2008
    All,I used the following and I get the error "Cannot create a connection to data source "SourceName"="/file c:FarmBillTesting.dtsx /Set Package.Variables[User::FilterValue].Properties[Value];" & Parameters!FilterValue.Value.ToString()What am I doing wrong?

  • Anonymous
    March 26, 2008
    hello Russell,I have SSIS and SSRS in the same machine, but I try to get data to other server. If i try to run de package dtsx, its run fine, but when I try to get the data trougth SSRS I don't receive anything.If I try to get data over a local database Its work fine.I need to have the data, SSIS and SSRS in the same server?

  • Anonymous
    April 23, 2008
    The comment has been removed

  • Anonymous
    May 07, 2008
    The comment has been removed

  • Anonymous
    May 12, 2008
    Yes - SSIS and SSRS must be on the same machine.

  • Anonymous
    June 10, 2008
    Not true... I have an SSRS report on MahineName: PC36279 referencing a package on MachineName: EMSSQLD1 pulling data from MachineName: EMSPEMS17 and everything works the same as if the SSRS report were on EMSSQLD1.

  • Anonymous
    June 10, 2008
    Cool! BOL says otherwise, however. Don't know if BOL is wrong and/or something has changed:http://technet.microsoft.com/en-us/library/ms345250.aspx

  • Anonymous
    July 09, 2008
    Not able to see the fields.I refreshed the dataset but still I am not able to view the fields of the dataset.

  • Anonymous
    August 03, 2008
    I have uncommented the SSIS in rsreportserver.config file.  Still Iam not able to get the SSIS in the type for the data source.Can you please help me out.ThanksSugavaneswaran

  • Anonymous
    August 15, 2008
    Hi russel!!i have ssis and SSRS on same machine. infact i am  trying in my desktop only. it is really not working for me. any thing i need to do??

  • Anonymous
    August 15, 2008
    sorry!! it is working for me. but i want to pass SSIS oledb connection string from SSRS. how can i do that??

  • Anonymous
    September 01, 2008
    The comment has been removed

  • Anonymous
    September 10, 2008
    The comment has been removed

  • Anonymous
    December 13, 2008
    I was able to run the report but I believe the parameters are work. When I do not include any parameter in the SSIS it works fine. My connection string is as follows:="/file C:ssisPackage.dtsx /Set Package.Variables[SelectMonth].Value;" & Parameters!SelectMonth.Value

  • Anonymous
    December 13, 2008
    Corrections to my last post:I was able to run the report but I believe the parameters don't work. When I do not include any parameter in the SSIS it works fine. My connection string is as follows:="/file C:ssisPackage.dtsx /Set Package.Variables[SelectMonth].Value;" & Parameters!SelectMonth.Value

  • Anonymous
    January 13, 2009
    The comment has been removed

  • Anonymous
    January 31, 2009
    The comment has been removed

  • Anonymous
    February 17, 2009
    I got it working. Try something like(copied from DTExecUI)/FILE "e:Visual Studio 2005ProjectsTable CompareTable CompareCompareRecord.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI  /SET "Package.Variables[User::Tablename].Properties[Value]";"QBIS$CA Activity Type" /SET "Package.Variables[User::PKName1].Properties[Value]";Code /SET "Package.Variables[User::PK1].Properties[Value]";AFSPRAAK

  • Anonymous
    March 20, 2009
    I'm having problems once I get to the steps outlined for "Create 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."  This is where I run into issues.  Can you be more specific about the steps you take please?  Kindly also just go through the remaining steps, because I think there is another error (calling it SSIS instead of the item that actually is in the list), etc.  Thank you so much.

  • Anonymous
    April 21, 2009
    The comment has been removed

  • Anonymous
    April 21, 2009
    The comment has been removed

  • Anonymous
    April 21, 2009
    The comment has been removed

  • Anonymous
    May 15, 2009
    The comment has been removed

  • Anonymous
    April 20, 2010
    Has anybody been able to use SSIS as a SSRS Datasource using sql 2008 and gotten it to work when deployed to sql2008 report server web?   I have used in sql2005 successfuly for several years.  Got new server installed sql2008 upgraded and deployed same reports. Can not get to work in sql2008.  Don't know why. Set everything up the same.

  • Anonymous
    April 20, 2010
    The comment has been removed

  • Anonymous
    March 21, 2011
    i don't know how to use .. sorry........