call a ssrs report in a ssis package


  • ssis package description step by step

    there are 10 flat files
      begin loop
        truncate table
        load one flat file at a time to table
        do some transformations
        ---help : i need to create a excel/PDF report using SSRS and put it in a folder
                  also every time i call a report i need to pass 2 parameters
                  (i can create excel sheet and dump things in every loop, but i need to do lot of formatting in a report so i need to use ssrs )
        create output flat file
      end loop

    CAN SOME ONE SUGGEST ME.. I CREATED THE .RDL FILE like the way i wanted. There a stored proc behind this rdl.. we need to pass 2 parameter to run the report.
    , all i need to do is how to run this report and put it in a folder????

    Friday, April 27, 2012 5:54 PM


All replies

  • Here's a good example of how you could use the SSRS webservice to accomplish what you are trying to do.  You would use a script task in ssis - may need to create the webservice proxy in a C# project and then add it manually to the SSIS project.  I think that is what I would up doing the last time I used a script task to execute a webservice.


    • Marked as answer by Dkuud Tuesday, May 01, 2012 11:39 PM
    Friday, April 27, 2012 6:03 PM
  • You would put your complete path and filename here: string fileName = @"c:\samplereport.xls";

    Then setup your parameters here:

    RE2005.ParameterValue[] parameters = new RE2005.ParameterValue[2];
          if (_parameters.Length > 0)
               parameters[0] = new RE2005.ParameterValue();
               parameters[0].Name  = "par1";
               parameters[0].Value = Variables.SSISVariable1.ToString(); //change SSISVariable1 to whatever the name of your SSIS variable is
              parameters[1] = new RE2005.ParameterValue();
               parameters[1].Name  = "par2";
               parameters[1].Value = Variables.SSISVariable2.ToString(); //change SSISVariable2 to whatever the name of your SSIS variable is


    Monday, April 30, 2012 8:23 PM
  • i am extremely poor at C#.. :-) basically i dont know...i assigned to parameters as read variable to my script taks , how to use these 2 parameters , i can also assign a file path as par3 to the script task. please suggest how declare and then use them..
    Monday, April 30, 2012 9:04 PM
  • You substitute your variables for the ones I called SSISVariable1 and SSISVariable2

    If you want to parameterize the path then you would change:

    string fileName =@"c:\samplereport.xls";


    string fileName =Variables.SSISVariable3.ToString();   //where SSISVariable3 is the name of your path variable


    Monday, April 30, 2012 9:24 PM
  • @oldjeep  ...i follwed but i was wondering on the namespaces for both  the webservices..

    but i get this error below .. suggest please.

    • Edited by Dkuud Tuesday, May 01, 2012 11:24 PM
    Tuesday, May 01, 2012 11:24 PM
  • okieeyy.......wooooohhooooooo it...i am the dumbest...
    Tuesday, May 01, 2012 11:38 PM