Reports within SQL 2005 Integration Services

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

  • 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?

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

  • 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?

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

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

  • 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

  • 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???

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

  • 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

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

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

  • 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

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

  • 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 19 total)

You must be logged in to reply to this topic. Login to reply