Calling Reporting Services from SSIS

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.

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

|||

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?

|||

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

|||

Thanks

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

I will try again.

Thanks again.

|||did anyone sucessfully run the .rdl file from ssis|||

What do you mean by "run an .rdl file". What exactly do you want to do?

-Jamie

|||i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.|||

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.

|||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|||I got an error on 'Save File'. It said declaration expected. I maybe missing some import statement that you have.|||here is my import list :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime|||Well I have the exact same thing. Did you have to declare SaveFile?|||please post your full class and FULL error message, it will be easier to help you|||

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