May 6, 2008 at 9:22 am
I'm just starting to learn SQL IS 2005 after years of DTS programming.
In DTS, I wanted to create some reports that would summarize the results of a database load process (files received, records processed, statistical summaries of the data just loaded to make sure the numbers make sense). To do this, I created a custom task that leveraged Crystal Reports 9 functionality to create a report and export the report to a PDF. I then used the email task (with the PDF as an attachment) to distribute to the people charged with monitoring the DTS uploads
Would love to get some different options on how to do this type of thing in SSIS 2005?
1) Could create the report in SSRS and fire it from SSIS. I've just briefly looked into this, appears to be bit tricky.
2) I've tried to replicate by Crystal Reports solution but having some trouble. Cannot access the Crystal components to add as a task for the Script task.
May 6, 2008 at 12:36 pm
How about creating the SSRS report and setup the report to run at a particular time after the job has had a reasonable amount of time to complete?
May 6, 2008 at 12:39 pm
We actually have a number of stored procedures that modify data every night and then the SSRS reports are setup to kick off at pre-scheduled times either every morning or once per week. They were all setup, prior to my arrival, with the assumption that the SP would complete without errors. Perhaps not the best method of doing things but you could have a log table that could be included in the report to indicate whether or not the last run successfully completed.
May 7, 2008 at 6:11 am
Yes, I understand the SSRS type solutions to create the report and run it.
However,
1) Is there anyway for me to trigger the creation of the SSRS report from SSIS as the last step of the SSIS process?
2) Have that report export its results to PDF
3) Be able to point the SSIS email task to the resulting PDF to include as an attachment?
May 8, 2008 at 7:17 am
1) Is there anyway for me to trigger the creation of the SSRS report from SSIS as the last step of the SSIS process?
2) Have that report export its results to PDF
3) Be able to point the SSIS email task to the resulting PDF to include as an attachment?
I'm not certain about your first question. I do know that you can link directly to the report's URL for live reporting but I'm certain that would yield what you are looking for. You might check into what the data driven subscription does versus the timed subscriptions, which is what we use. I don't know what or how the data driven subscription does.
I do know that the email subscriptions can be setup to export to PDF and send it as an attachment.
May 10, 2008 at 6:50 am
Hi,
Yes you can use SSIS Script task to create SSRS reports, refer link below for more information on how to call SSRS reports using VB,
http://www.tek-tips.com/faqs.cfm?fid=5918
Then you can use Send Mail task or Script task to send mail with PDF attachment.
Please check and let me know.
Regards,
Omkar.
May 22, 2008 at 10:36 am
Yes you can do this the code that needs to go in your script task is below, I will leave it to you to optimize this further as this was just a hack to ensure that it could be done.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'pass inthe URL of the reportserver/folder/report
'choose the render format using rs:Format in this case PDF
'set the name of the file in this case Todays date and the Filename
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fFolderName%2fReportName&rs:Command=Render&rs:Format=PDF", Dts.Variables("varExportDir").Value.ToString() + Format(Now, "yyyydM") + ".NameOfSavedFile.pdf")
Dts.TaskResult = Dts.Results.Success
End Sub
'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)
'you can either use the user executing the package
loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
loRequest.ImpersonationLevel = Security.Principal.TokenImpersonationLevel.Delegation
' or explicitly set the credentials
'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
'MsgBox(ex.ToString)
'End Try
System.Threading.Thread.Sleep(2000) ' Sleep for 1 second
End Sub
End Class
Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. ~ Albert Einstien
May 25, 2008 at 6:13 pm
Hi
I'm also attempting to get this working. I've tried both methods mentioned above.
The script under the tek-tips link always gives me the IDE message "Type 'ReportingService.ReportingService' is not defined"... even though I'm fairly sure I've added all the required references. (I'm a newby to .Net so could well have missed something here)
I've seen someone else mention a "Microsoft.ReportingService" dll which I haven't been able to locate, although I have other very similar ones. Anyone know where I can find this???
Then the script suggested in the previous post with the SaveFile method appears to work as it does create the file, however the file always appears to be corrupted. I've tried saving as PDF, EXCEL, TIFF & HTML all with no luck. Has anyone else managed to get this to work?
Any ideas???
May 26, 2008 at 1:15 am
Hi again
I've been trying to figure out why the files which are being generated in the script are corrupted.
If I browse to a report using IE and then append "&rs:Command=Render&rs:Format=PDF" to the url there is no difference in how the report is displayed. Am I right that I should expect it to be rendered as a PDF automatically??? The same applies to other file types (EXCEL etc).
If that's the case does that suggest there is an incorrect setting within Reporting Services???
Thanks in advance for any suggestions.
May 26, 2008 at 2:28 am
That depends on your complete URL. I scrubbed the code to remove anything that might be under NDA for my company and may have messed up the URL. I have reviewed it and dont see it but that may not mean anything, as I am admittedly a bit drunk ATM. That said you should be able to add the rs:Command=Render&rs:Format=PDF and have it work. I use this code daily in our production environment. This link http://msdn.microsoft.com/en-us/library/ms152835.aspx shows how to use the URL and this one specifies the available Render Formats http://msdn.microsoft.com/en-us/library/ms154040.aspx.
Forgive me if I should have quoted that or put it in code blocks as I rarely post. Another option you have is to leverage the FireEvent() of the ReporService2005.asmx. Making this work in SSIS is a bit involved as it requires you to write a wrapper class for the web service, strong name the resulting dll and put it in the GAC ( also in the .net framework folder for use in the IDE). If anyone is interested in this type of solution let me know and I will post the code as well.
Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. ~ Albert Einstien
May 27, 2008 at 4:10 pm
Hi
I've now managed to get this working.
My lack of SSRS experience was the problem. I'd copied the url from the address bar in Report Manager.
When I changed the "/Reports/Pages/Folder.aspx?ItemPath=..." to "/ReportServer?%2f..." everything worked perfectly.
Thanks very much for posting this code. Great help.
Todd.
June 19, 2008 at 10:06 am
hi
i am having a same problem i have create a report for wach orders in the table in a pdf format and send it to the subscriber and move that report to some folder.i was trying to go through the vb code but i could not figure it out. can you explain in it in a detail way how to approach .
many many thanks.
June 20, 2008 at 12:21 pm
bishals,
What specific errors are you getting? It would help if I knew what you were trying to accomplish and how you were going about it. I assume you have modified the code to meet your needs if you can post that code I and some details about what you need to do I will take a look at it and see if I can point you in the right direction.
Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. ~ Albert Einstien
June 20, 2008 at 12:52 pm
i have seen the code i am not an expert of visual basic so i had a problem in understanding the code . i have to create a report from the data and the report has to be in a specific format and the report name should be dynamic depend upon the certain values of the data coming in the data dlow task and finally i have to mail the report to people depending upon the are assigned the data and there are more than 5 address of the people . i just dont know how to use a ssis data as a data in reporting aservises and create a pdf form of report from it.
can u explainme it in detail . save me i am strugling on it since more than a wee.
September 9, 2008 at 8:20 am
Hello Guys
I am having the same issue as "Type 'ReportingService.ReportingService' is not defined"... Is there any solutions?? I am using VS 2008 and and 2005 SQL Reporting Services.
Thank you
Murali
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply