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

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

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)

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.

so you created all thoses variables and setted all thoses variables in the "read only variables" field of the script task ?

I got the error to go away. Where is the DTSCONFIG file located?

Where ever you had password, url, login or domain, I typed in the script. Am I supposed to put it somewhere else?

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?

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.

Did you try the code Joseph provided? It should work.

If you have the report hosted on the IIS you should be able to access it simply using the Script Task

This script task is "working" but my Excel file is blank. What is a reason for this. No errors were generated

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

This is also working for me with no error message, though the file is blank....

This one line message is not very helpful.Kindly expand on the problem. Right answer requires a right question.

can you hel me to find out why thereport 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...

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

Thanks.. I am trying torun the RS file toexcel 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

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

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.

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?

"I have the same problem ...

"

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

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

Could you possibly post the code or email it?

jkrishnaswamy@comcast.net

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!

What about a web services task?

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.

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

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?

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?

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.

wow wow wow this is very nice stuff y'all! works perfectly! thanks so much!

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.

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

awesome thank you!!!!

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

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

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

 

Thanks a lot!

Thanks a lot! This thread finally solved my problem.

Thanks. Works fine!

Exporting Report from SSIS is possible via SSRS Webservice Call using VB.net or C# code. But if you are not a coder and you want simple drag-drop approach then check our 3rd party task.

	Name	Features
	Reporting Services (SSRS) Task	Automate report export/email process. Support for SSRS 2005/2008/2012. Support for Sharepoint Integrated Mode. Export report in xls, pdf, doc, xml, mhtml and csv format Support for Data Driven export process. You can configure reports, recipients, file format and many other options for automated export Read more