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:


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


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


http://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 (54)

  1. Anonymous says:

    I was working on a similar requirement and your post was really helpful. I’m trying to pass a date parameter to a package from an SRS report (using the method that you describe) and I get an "SSIS execution context is not valid" error. However, when I remove the /Set portion of the expression, things work fine.

  2. Anonymous says:

    I solved the problem that I posted. Basically, I had to surround the date parameter within quotes.

  3. Anonymous says:

    Hi,

    I am unable to find the SSIS data source type.

    Does anyone know why this could happen?

    thanks

  4. 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

  5. Anonymous says:

    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

  6. Anonymous says:

    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

  7. 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.

  8. Pradeep — Yes, I beleive this is possible although I haven’t tried it myself. You will need to have the SSIS components installed on your machine, however. In general SSRS & SSIS must be installed on the same machine for this all to work in a production enviornment…

  9. Anonymous says:

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

  10. Anonymous says:

    I have done everything here but I can’t connect the report to the datareader for some reason.  I am not using parameters and I believe that the connection string is correct it is: "/FILE "C:Documents and SettingstkingMy DocumentsVisual Studio 2005ProjectsTestRpt_Finance_AS400_Summary_Tables_RefreshSummaryTables.dtsx"

    What is the exact syntax for the query box with the datareader?  Is it select * from datareaderdest?  With the type of text? I get an error that states:

    "The specified data flow component was not found in the package. (Microsoft.ReportingServices.Designer)"  

    I am so close but something is wrong.

  11. Anonymous says:

    hi, how can i use ssis packages in reporting models

    ive been able to it in reporting services but when i try this in a report model project i cant connect to an ssis package

    can u help me plz?

    bkkoun mamnounkon

  12. You cannot use SSIS packages as the source of a report model: We only support SQL, SSAS, and Oracle (in SP2)

  13. Anonymous says:

    I can see the fields in my dts Package, can even change the field name and it recognizes the change, but everytime I try to run it, it gives me an error – The Package Failed to Execute.  

    I am using the MS version for the datasoure (-f "C:package.dtsx").  If I use this (= "/file C:package.dtsx") it give me an error saying expression are not allowed.

    I am using VS 2005.  What am I missing?

    Thanks!

  14. Anonymous says:

    Can a report sourcing from SSIS be run over the web server?  Runs fine in design studio but I keep getting connection errors regardless if I try valid local, domain or db users while configuring the execution account – Thanks!

  15. 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?

  16. Anonymous says:

    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.

  17. 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.

  18. Anonymous says:

    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.

  19. Anonymous says:

    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.

  20. Anonymous says:

    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 Authentication

    In 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 …

  21. Anonymous says:

    When i add the parameterized connection string as stated above i get an error "A connection cannot be made to the database.Set and test the connection string. –and –The SSIS execution syntax is not valid."

    The string is below:

    /file"C:WWCI-BTI_Financials.dtsx /SetPackage.Variables[StartDate];" & Parameters!StartDate.Value

    When i take the parameter section out it works fine. The entire dataset.  Any ideas on what i left out.

  22. Anonymous says:

    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.Value

    If 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.Value

    Hope this helps.

  23. Anonymous says:

    My connection string is as follows:

    ="/file d:AFELogSouthCopy.dtsx /Set AFELogSouthCopy.Variables[AFE_Report_ID];" & Parameters!AFE_Report_ID.Value

    When 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??

  24. Anonymous says:

    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??

  25. rvillaronga says:

    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?

  26. Anonymous says:

    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:

    1. Modification in RSReportDesigner file.
    2. Modification in rsreportserver.Config file

    3. Creating SSIS package with same variables.

    4. Executing Package.

    5. Creating the dataset.

    6. Selecting Type as SSIS and mentioning below mentioned path as a connection String

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

    1. Mentinoning DataReaderDestination name as DataReaderDest in the QueryString.
      1. Type as Text

    Still the Error is coming ;

    Connection can not be made to the Datasource..

    Can anyone help me out to resolve the issue?

  27. Anonymous says:

    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?

  28. 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?

  29. Anonymous says:

    Data can be form any where, but i have done and implemented paramerized (Dynamic) connection string to call SSIS package from SSRS(With /File option). Actually it calls dtexec from behind. In this case you need to have SSIS and SSRS on the same box (VERY VERY IMPORTANT). Else SSIS won’t work

    Kapil

    kap_gemini@yahoo.com

  30. Anonymous says:

    PC-A Developer PC with BIDS installed

    PC-B Application Server & SSIS Server

    PC-C SQL Server

    On PC-A i have a dtsx and rdl file within BIDS project, i try to run ssrs report which is accessing SSIS as DataSource, i can make the connection successfully to ssis but i get the error "An Error has occurred during report processing. Query execution failed for data set datasetname’. The package failed to execute"

    Can anyone help me solve this problem, i have tried all above suggessted options but to no avail.

    Is this because there is no SSIS installed on PC-A as my current assumption is within BIDS you should be able to run successfully.

  31. Yes – SSIS and SSRS must be on the same machine.

  32. Anonymous says:

    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.

  33. 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

  34. Anonymous says:

    Not able to see the fields.

    I refreshed the dataset but still I am not able to view the fields of the dataset.

  35. Anonymous says:

    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.

    Thanks

    Sugavaneswaran

  36. Anonymous says:

    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??

  37. Anonymous says:

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

  38. Anonymous says:

    Hi,

    I am executing SSRS report which takes a Simple SSIS(no parametres passed,package pulls data from an XML files and loads a DataReader destination) package as source,it works and previews all data in Report S=Designer but when the Report is deployed it gives Error in Execution from Report Manager …’Package failed to execute’,to debug if the package is called internally i have added logging but found only prevalidations are occuring and its exiting without entering in ‘Packagee Start’ ..neither logging any error..please suggest me a soltion.

  39. Anonymous says:

    I have the same problem as Nabarun. Everything is fine in Designer but Report Server fails to execute the SSIS package even the service account is granted all necessary rights. I am running sql2008 on Windows 2008. Any thoughts?

    Thank you very much!

  40. Anonymous says:

    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

  41. Anonymous says:

    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

  42. Anonymous says:

    I’m having a similar Problem to Vicky.  I have SQL 2008 Enterprise installed on a Windows 2008 server (both x64), and am trying to use an SSIS package to be the source to an SSRS report.  I’ve made the package as simple as possible, it has one trusted connection to the database and sends the data to a datareader destination.  

    As with everyone else posting here, everything works when previewing the report.  I’ve run the package on the server itself and no errors.  I’ve run SQL profiler and verified that the package is connecting to the database properly with the proper credentials when trying to execute the package from a report (a step up from SSRS2005).  

    I added in some logging to the package, the error reported is ‘component "DataOut" (31) failed initialization and returned error code 0x8007000E. ‘  Any ideas what may be causing this component to fail?  

  43. Anonymous says:

    I have the same problem. Configured everything to the last detail. Using SSIS/SSRS 2008. SQL Server Service accounts have local admin rights (for testing). Keep getting:

    An error occurred during client rendering.

    An error has occurred during report processing.

    Query execution failed for dataset ‘DataSet1’.

    The package failed to execute.

    I’ve looked everywhere for a solution to this. Any ideas?

    Appreciated

  44. Anonymous says:

    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

  45. Anonymous says:

    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.

  46. Anonymous says:

    I tried pulling from SSIS to SSRS by following the above steps mentioned.I am facing the same error like "The SSIS execution syntax is not valid".

    Connection string I have given is

    "/file D:sfhsfhsfh.dtsx"

  47. Anonymous says:

    I tried pulling from SSIS to SSRS by following the above steps mentioned.I am facing the same error like "The SSIS execution syntax is not valid".

    Connection string I have given is

    "/file D:sfhsfhsfh.dtsx"

  48. idhris says:

    I tried pulling from SSIS to SSRS by following the above steps mentioned.I am facing the same error like "The SSIS execution syntax is not valid".

    Connection string I have given is

    "/file D:sfhsfhsfh.dtsx"

  49. John S says:

    I’m getting the same problem as several people above.  i can preview the report in the report designer but when deployed I get the error message:

    An error has occurred during report processing.

    Query execution failed for data set ‘SSISPackage’.

    The package failed to execute.

    Has anyone found a solution to this?

  50. dmurphy says:

    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.

  51. dmurphy says:

    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.

    Forgot to mention.  It all works fine in design Visual Studio 2008.  Only fails when deployed to web. Get this error message:  

    An error occurred during client rendering.

    An error has occurred during report processing.

    Query execution failed for dataset ‘name_of_my_dataset’.

    The package failed to execute.

  52. vishal patel says:

    i don't know how to use .. sorry……..