none
Calling Reporting Services from SSIS

    Question

  • Hi

     

    I have created a packages which pull and push the data to SAP server.

    I want to create a report every day and send that report to the manager.

    For the same i want to call reporting services in my SSIS package.

    I know i can write a SQL script and export the report in excel but i want to use Reporting services.

    Have any one call reporting services from ssis.

     

     

     

    Friday, November 03, 2006 11:35 PM

Answers

  • I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

    Thanks for the help.

    Thursday, January 18, 2007 9:25 PM

All replies

  •  bhalchandra.kunte wrote:

    Hi

     

    I have created a packages which pull and push the data to SAP server.

    I want to create a report every day and send that report to the manager.

    For the same i want to call reporting services in my SSIS package.

    I know i can write a SQL script and export the report in excel but i want to use Reporting services.

    Have any one call reporting services from ssis.

     

     

     

    What do you mean by "call Reporting Services"?

    RS is a web service that you can call from anythig that supports calling web services. including SSIS.

    -Jamie

     

    Saturday, November 04, 2006 3:47 AM
    Moderator
  • In SSIS  we can call/work on analysis within SSIS package

    by using tools which are provided in toolbox...

     --ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

    --ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

     

    Can we  work / call  Reporting Services within SSIS package?

     

     

     

    Sunday, November 05, 2006 11:14 PM
  •  bhalchandra.kunte wrote:

    In SSIS  we can call/work on analysis within SSIS package

    by using tools which are provided in toolbox...

     --ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

    --ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

     

    Can we  work / call  Reporting Services within SSIS package?

     

     

     

     

    There are no tasks within SSIS to do anything with Reporting Services. However, RS has an API which you can call from SSIS using the Web Service Task or (I suspect) a Script Task.

    It would help if you could be more specific about what you want to do.

     

    -Jamie

     

    Monday, November 06, 2006 12:33 AM
    Moderator
  • Thanks

    I have already tried that but not managed to get it done.

    I will try again.

    Thanks again.

     

     

    Tuesday, November 07, 2006 11:23 AM
  • did anyone sucessfully run the .rdl file from ssis
    Thursday, January 18, 2007 12:17 AM
  • What do you mean by "run an .rdl file". What exactly do you want to do?

    -Jamie

     

    Thursday, January 18, 2007 12:33 AM
    Moderator
  • i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.
    Thursday, January 18, 2007 12:47 AM
  • I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

    Thanks for the help.

    Thursday, January 18, 2007 9:25 PM
  • I managed to generate a SSRS report  directly from a script task within SSIS :

    i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

    varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

    varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN  : A windows user log/pass allowed to generate reports on the server (used for authentification)


    //Sample call
    SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

    //Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format


    //The get & save file method
    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
            Dim loRequest As System.Net.HttpWebRequest
            Dim loResponse As System.Net.HttpWebResponse
            Dim loResponseStream As System.IO.Stream
            Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
            Dim laBytes(256) As Byte
            Dim liCount As Integer = 1

            Try
                loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
                loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
                loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
                loRequest.Method = "GET"
                loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
                loResponseStream = loResponse.GetResponseStream
                Do While liCount > 0
                    liCount = loResponseStream.Read(laBytes, 0, 256)
                    loFileStream.Write(laBytes, 0, liCount)
                Loop
                loFileStream.Flush()
                loFileStream.Close()
            Catch ex As Exception

            End Try

        End Sub

    Friday, January 19, 2007 11:10 AM
  • I got an error on 'Save File'.  It said declaration expected.  I maybe missing some import statement that you have.
    Wednesday, January 31, 2007 4:55 PM
  • here is my import list :

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Wednesday, January 31, 2007 5:03 PM
  • Well I have the exact same thing.  Did you have to declare SaveFile?
    Wednesday, January 31, 2007 5:12 PM
  • please post your full class and FULL error message, it will be easier to help you
    Wednesday, January 31, 2007 5:19 PM
  • I am using what you had posted:

    //Sample call
    SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

    //Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format


    //The get & save file method
    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
            Dim loRequest As System.Net.HttpWebRequest
            Dim loResponse As System.Net.HttpWebResponse
            Dim loResponseStream As System.IO.Stream
            Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
            Dim laBytes(256) As Byte
            Dim liCount As Integer = 1

            Try
                loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
                loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
                loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
                loRequest.Method = "GET"
                loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
                loResponseStream = loResponse.GetResponseStream
                Do While liCount > 0
                    liCount = loResponseStream.Read(laBytes, 0, 256)
                    loFileStream.Write(laBytes, 0, liCount)
                Loop
                loFileStream.Flush()
                loFileStream.Close()
            Catch ex As Exception

            End Try

        End Sub

     

    As soon as I paste it, I get an error on SaveFile, it says, "declaration expected."

    Wednesday, January 31, 2007 5:23 PM
  • You should not use this code as this ... because i use variables that must be declared and passed to the script task ...

    you forgot to read this part of my original message :

    i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

    varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

    varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN  : A windows user log/pass allowed to generate reports on the server (used for authentification)

    Wednesday, January 31, 2007 6:03 PM
  • I did read the entire message.

    I set the URL, Login, Password and Domain names.  I just didn't want to put that information out on the Internet, so I left it the way it was. 

    Wednesday, January 31, 2007 6:43 PM
  • so you created all thoses variables and setted all thoses variables in the "read only variables" field of the script task ?
    Wednesday, January 31, 2007 6:51 PM
  • I got the error to go away.  Where is the DTSCONFIG file located?
    Wednesday, January 31, 2007 6:52 PM
  • Where ever you had password, url, login or domain, I typed in the script.  Am I supposed to put it somewhere else?
    Wednesday, January 31, 2007 6:55 PM
  • I created my variables at SSIS > Variables, and set those variables in the "read only variables" field of the script task?  Is the varSSRS_URL the same as the varSSRS_Domain?
    Wednesday, January 31, 2007 7:51 PM
  • How were you able to get it to work?  I am trying to run a RS report withing SSIS, then export that report to a pdf file and then email that pdf file as an attachment.  Please reply when you can. 

     

    Wednesday, February 20, 2008 9:45 PM
  • Did you try the code Joseph provided? It should work.

     

    Thursday, February 21, 2008 1:19 AM
    Moderator
  • If you have the report hosted on the IIS you should be able to access it simply using the Script Task

    Thursday, February 21, 2008 8:27 PM
  • This script task is "working" but my Excel file is blank. What is a reason for this. No errors were generated

     

    Wednesday, April 02, 2008 3:37 PM
  • In VS2005 the reports were hosted on IIS. In VS 2008 there is a ReportServer that hosts report related objects.
    I created a report using the Report Server Project and deployed it on the ReportServer.
    Then I created an ActiveX Script task with this simple function,

    Set oIE=CreateObject("Internet Explorer.Application")
    oIE.navigate "http://hodentek2:8080/Reports/Pages/Report.aspx?ItemPath=%2fRSProject%2fpubsAuthors" 'ÚRL of my 'Report manager
    oIE.Visible=true

    When I executed this package I could see the report displayed in IE.
    If you can browse the report from where it is hosted you can copy that URL in your script.

    May be you should take a look at my book on SSIS [although it is meant only for beginners]
    Beginners Guide to SQL Server Integration Services using VS 2005
    Thursday, April 03, 2008 2:54 PM
  • This is also working for me with no error message, though the file is blank....
    Tuesday, April 15, 2008 9:59 AM
  • This one line message is not very helpful.Kindly expand on the problem. Right answer requires a right question.

    Friday, April 18, 2008 3:32 PM
  • can you hel me to find out why the report  output is blank?  I used your code to call RS report inside of SSIS..
    I didn;t get any errors but the output is balnk...
    Sunday, May 04, 2008 4:58 PM
  • I went back and tried and it works. If you would like, I can email my package. But take a look at this note in my blog:

     

     

     

    http://hodentekhelp.blogspot.com/2008/05/activex-script-task-to-access-report-on.html

     

     

    Thursday, May 08, 2008 4:02 PM
  • Thanks.. I am trying to run the RS file to excel and I've used the below script.. it generates the file but it;s blank.. i am not sure what i am doing wrong.. any help??

     

    --here is the script i put it in to scrip task in control flow..

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

     

     

    Public Class ScriptMain

     

    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)

    Dim loRequest As System.Net.HttpWebRequest

    Dim loResponse As System.Net.HttpWebResponse

    Dim loResponseStream As System.IO.Stream

    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)

    Dim laBytes(256) As Byte

    Dim liCount As Integer = 1

    Try

    loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)

    loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_Login").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())

    loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes

    loRequest.Method = "GET"

    loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)

    loResponseStream = loResponse.GetResponseStream

    Do While liCount > 0

    liCount = loResponseStream.Read(laBytes, 0, 256)

    loFileStream.Write(laBytes, 0, liCount)

    Loop

    loFileStream.Flush()

    loFileStream.Close()

    Catch ex As Exception

    End Try

    End Sub

     

     

    Public Sub Main()

    '

    ' Add your code here

    '

    SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2ffWITSExtract%2fYVoucherAjudication&rs:Command=Render&rs:Format=EXCEL", outpath + "C:\Report.excel")

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

    Tuesday, May 13, 2008 7:51 PM
  • I've used the bleow script to run a RS in SSIS and export the file to EXCEL..it generates a file .. but the content is blank..

    i am not sure  what i am doing wrong.. any help??

     

     

     

    --here is the script i put it in to scrip task in control flow..

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

     

     

    Public Class ScriptMain

     

    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)

    Dim loRequest As System.Net.HttpWebRequest

    Dim loResponse As System.Net.HttpWebResponse

    Dim loResponseStream As System.IO.Stream

    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)

    Dim laBytes(256) As Byte

    Dim liCount As Integer = 1

    Try

    loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)

    loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_Login").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())

    loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes

    loRequest.Method = "GET"

    loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)

    loResponseStream = loResponse.GetResponseStream

    Do While liCount > 0

    liCount = loResponseStream.Read(laBytes, 0, 256)

    loFileStream.Write(laBytes, 0, liCount)

    Loop

    loFileStream.Flush()

    loFileStream.Close()

    Catch ex As Exception

    End Try

    End Sub

     

     

    Public Sub Main()

    '

    ' Add your code here

    '

    SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2ffExtract%2fYVoucher&rs:Command=Render&rs:Format=PDF", outpath + "C:\Report.pdf")

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

    Tuesday, May 13, 2008 7:53 PM
  • I assume you have used the correct  URL. If port 80 is used for somethng else, the default port for SSRS could be 8080 in which case your url should indicate that.

     

    Wednesday, May 28, 2008 11:03 AM
  • I have the same problem ... the file is blank and if we generate a csv too.
    So I opened them with notepad and I could see that it worked ... but what was copied in the file is the source code of the webpage displaying the report ... not the report itself ...

    Isn't there a way to run the reporting service API instead?


    Friday, September 05, 2008 6:05 AM
  • "I have the same problem ...

    "

    Could you explain a little more about what you are trying to do?

     

    Friday, September 05, 2008 3:53 PM
  • I'm trying to export the report as a pdf file with a script task. The same action as when we manually click on the export button after visualizing the the report.

    I tried this method but that creates a blank invalid file.

    I added a reference to "Reporting Service" (it's in the .net assembly list) but couldn't figure yet how to use it ...


    Sunday, September 07, 2008 11:44 PM
  • Could you possibly post the code or email it?

     

    jkrishnaswamy@comcast.net

    Monday, September 08, 2008 9:57 PM
  • Well, in fact I found how to do that.
    I used the example you can find at this address:

    http://www.codeplex.com/MSFTRSProdSamples/Wiki/View.aspx?title=SS2005!File%20Share%20Data%20Processing%20Extension%20Sample&referringTitle=Home

    Download the sample files and then go to C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Application Samples and take a look at the solution FindRenderSave Sample

    If you paste the code, it should work (of course modify it to your convenience) but you should get some errors like "cannot use that statement multiple times" or "this variable is already declared" etc ...
    I think that's due to a specificity of Visual Studio for Apllications.

    The way I found to correct that is to place the code in a module, not directly in the main task's class.

    I hope that helped!



    Tuesday, September 09, 2008 12:56 AM
  • What about a web services task?
    Sunday, September 14, 2008 8:10 PM
  • I tried a web service to call Report Server. I do get connected for the RS URL and  WSDL I generated. Bt the RS2005 format is not supported. I cannot see the methods. I did post it to the SSIS forum.
    Sunday, September 14, 2008 8:16 PM
  • I'm not very familiar with web services ... I had tried to generate the report with the link inlcuding the command such as "rs:Render", the same as when we click on "export" manually but that didn't work.

    To me, using the API was the est and safest solution but I don't really know advantages of web services ...
    Monday, September 15, 2008 12:17 AM
  • URL access is the best and easiest. The thread started with a question related to accessing Report Server from a Integration Services task and hence all this discussion.
    The question I was trying to answer was, Whether it is possible to call the reporting services from a SSIS's Web Service Task or not?

    Tuesday, September 16, 2008 2:38 AM
  •  

    I am able to use the code in the example provided and generate the report in the excel format.  However when I tried to create the same in pdf, it is blank.  Any ideas?
    Tuesday, October 21, 2008 4:21 PM
  • As to displaying a report in pdf using url, it works fine as in:


    http://hodentek2/ReportServer/Pages/ReportViewer.aspx?/MyWebStudents&rs:Format=PDF&rs:Command=Render




    As to the question of using a Web Service Task to access RS in SSIS a resolution is not available at this time. It appears it will happen in a future major revision.
    Tuesday, October 21, 2008 10:03 PM
  • wow wow wow this is very nice stuff y'all! works perfectly! thanks so much!
    Saturday, November 14, 2009 12:24 AM
  • If you want to save on any programmatic effort OR if you need this functionality implemented in many or most of your packages, check out this: http://siddhumehta.blogspot.com/2009/11/how-to-generate-ssrs-report-output-by.html

    Hope it helps.
    --Siddharth Mehta http://siddhumehta.blogspot.com
    Saturday, November 14, 2009 2:21 AM
  • Hi Guys,
    I have been playing with this code and have made some changes which may help someone in the future:

    1. Credentials:

    I didn't want to embed plain text passwords/credentials in my package so instead of the line:

    loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables( "varSSRS_Login" ).Value.ToString(), Dts.Variables( "varSSRS_PASSWORD" ).Value.ToString(), Dts.Variables( "varSSRS_DOMAIN" ).Value.ToString())


    You can use this line to pass through current credentials:
    loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials

    2. Debugging

    If you are getting empty files created try putting something to display the reason in a message box.

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

    When I was trying I found that Authentication or incorrect report names (i.e. the URL being passed to ReportingServices was not correct).

    Good Luck.

    Cheers,
    Mike
    • Proposed as answer by Yosias Wednesday, January 13, 2010 11:13 PM
    Wednesday, January 13, 2010 10:18 PM
  • awesome thank you!!!!
    Wednesday, January 13, 2010 11:17 PM
  • Check out a couple of examples on my blog that ilustrate how to call the webservice in Control Flow and Dataflow.  This nice bit about generating in dataflow is that you can save your results directly to SQL Server if you want to archive them before you distribute them to a whole load of recipients.

    http://sqldeveloperramblings.blogspot.com/2010/02/generate-ssrs-reports-in-ssis-report-1.html

    Thursday, March 18, 2010 12:04 PM
  • For everyone with blank reports, this is kind of late but I just got this working myself!

    I removed all references to the variables first and just had strings for my paramters. Anything with a "Dts.Variables"

    The issue was there. Once you get that working, feel free to add them back in one by one.

    Also use the code Mike provided below to help debug.

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
     

    - Vimal

    www.livelogic.net

    Friday, June 18, 2010 1:09 AM
  • I used the same code , found no errors but when tried to open the Xls sheet gave me this message -

    missing file .. rwebcontrol.axd?optype=Stylesheet&version=2005.090.1390.00 .... the excel was an empty sheet ... can you suggest a work around please ?

    thanks

     

    Wednesday, September 29, 2010 12:56 PM
  • Thanks a lot!
    Friday, October 08, 2010 7:39 PM
  • Thanks a lot! This thread finally solved my problem.
    Monday, July 25, 2011 7:37 PM
  • Thanks. Works fine!
    Thursday, June 14, 2012 4:54 PM
  • i need to do same senario how i can do that?

    Wednesday, November 09, 2016 7:54 PM