need to create ssis package to run crystal report

  • need to create ssis package to run crystal report(s); I want to read a sql table and return values as parameters to cyrstal reports, and then email the report. Has anyone been able to do this? Thanks in advance for your help.

  • Off the top of my head I have never seen this accomplished..

    But if you have the Crystal libraries installed on the machine with SSIS, I would think you could use them in a Script task to do what you want to do.. It would be like building an app in VB to do it, only SSIS would be the container and not an EXEC..

    Anybody else got any good ideas?

    CEWII

  • Richard Cranston (10/28/2009)


    need to create ssis package to run crystal report(s); I want to read a sql table and return values as parameters to cyrstal reports, and then email the report. Has anyone been able to do this? Thanks in advance for your help.

    I don't have too much experience with Crystal Reports, but I think you will have to do a bit of programming. You may find these resources helpful:

    Loading the Output of a Local Package

    Configuring Reporting Services to Use SSIS Package Data

    The idea is that you can use an SSIS package as ADO.NET source in your reports.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • There are some third-party utilities available which allow you to run a Crystal Report from the command line. So, once you have that working, your problem reduces to building the correct command line, with parameters in place, and then executing that from within SSIS - which isn't so hard.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for all the replies...yes, I have thought about third party software to build command line; and I have not used Reporting Services at all yet. I think using vb script within ssis is what might work for me....Thanks again for all the help.

  • I was finally able to run a sssis package with variables passing to "Script Task" which in turns used Crystal Resources to genereate a pdf file. I had another task to email the pdf afterwards. Took some doing espically to copy the Crystal ddl files so ssis could add them as references. Also took some time to correct the login information and export options. I have some hard coded values....will have to tighten things up before production, but at least runs now on my pc....now see if I can redo this on my production box.

  • I need to do this too...will you share any secrets

  • The first thing you have to do is copy the four crystal reports dll files into the sql server folder so they can be added in the ssis script reference; CrystalDecisions.CrystalReports.Engine.dll, CrystalDecisions.ReportSource.dll, CrystalDecisions.Shared.dll, CrystalDecisions.Windows.Forms.dll into C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies or your appropriate version folder.

    Then add a Script Task; and the open the Design Script to edit it. Add the above four dll into the References.

    Then add the following in the General Declarations:

    Imports CrystalDecisions.CrystalReports

    Imports CrystalDecisions.ReportSource

    Imports CrystalDecisions.Shared

    Imports CrystalDecisions.Windows.Forms.CrystalReportViewer

    Here is some more code: Some values such as the locations of both reports are hardcoded for now and I am passing

    variables for the crystal reports parms. Change these report locations, and change the LogonToDatabase connections.

    If you have subqueries, may require more login connections.

    This creates a pdf report and I add another email task to email this pdf report to me.

    Public Sub Main()

    Dim CrystalReportViewer1 As New CrystalDecisions.Windows.Forms.CrystalReportViewer

    Dim DiskOpts As New CrystalDecisions.Shared.DiskFileDestinationOptions

    Dim myExportOptions As New CrystalDecisions.Shared.ExportOptions

    DiskOpts.DiskFileName = "C:\SalesAnalysisYearToDate_RC.pdf"

    myExportOptions.ExportFormatType = myExportOptions.ExportFormatType.PortableDocFormat

    myExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile

    myExportOptions.ExportDestinationOptions = DiskOpts

    ' Verify the path to the Crystal Report's .RPT file:

    Dim strReportPath As String = "C:\CrystalReportTest\MyCrstalReport.rpt"

    If Not System.IO.File.Exists(strReportPath) Then

    Throw (New Exception("Unable to locate report file:" & _

    vbCrLf & strReportPath))

    End If

    ' Load the Crystal report's .RPT file:

    Dim cr As New CrystalDecisions.CrystalReports.Engine.ReportDocument

    cr.Load(strReportPath)

    'Set the logon credentials of the main report---change these values

    LogonToDatabase(cr.Database.Tables, "MySqlServerName", "MyDatabaseName", "MyPassword")

    ' Set parms from variables--you may not need these

    cr.SetParameterValue("BusinessUnit", Dts.Variables("User::BusinessUnit").Value)

    cr.SetParameterValue("Plant", Dts.Variables("User::Plant").Value)

    cr.SetParameterValue("Division", Dts.Variables("User::Division").Value)

    cr.SetParameterValue("FiscalYear", Dts.Variables("User::FiscalYear").Value)

    cr.SetParameterValue("FiscalMonth", Dts.Variables("User::FiscalMonth").Value)

    cr.SetParameterValue("Dimension", Dts.Variables("User::Dimension").Value)

    cr.SetParameterValue("Export", Dts.Variables("User::Export").Value)

    cr.SetParameterValue("TopCount", Dts.Variables("User::TopCount").Value)

    ' Set the CrystalReportViewer's appearance and set the ReportSource:

    ' This may not be needed but I kept them anyhow

    CrystalReportViewer1.ShowRefreshButton = False

    CrystalReportViewer1.ShowCloseButton = False

    CrystalReportViewer1.ShowGroupTreeButton = False

    CrystalReportViewer1.ReportSource = cr

    cr.Export(myExportOptions)

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Private Sub LogonToDatabase(ByVal ReportTables As CrystalDecisions.CrystalReports.Engine.Tables, ByVal ServerName As String, ByVal UserId As String, ByVal Password As String)

    ' To Supply Logon Information to each and every Tables used in the Report

    Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

    Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()

    Dim myLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()

    myConnectionInfo.UserID = UserId

    myConnectionInfo.Password = Password

    myConnectionInfo.ServerName = ServerName

    myLogonInfo.ConnectionInfo = myConnectionInfo

    For Each myTable In ReportTables

    myTable.ApplyLogOnInfo(myLogonInfo)

    Next

    End Sub

    These websites helped me...

    http://www.thescarms.com/dotnet/CrystalRptViewer.aspx

    http://msdn.microsoft.com/en-us/library/aa288404(VS.71).aspx

    And see attached files helped me.

  • Thanks...can't wait to give it a try!

  • But when click on Add Refence and browse the dll from

    C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies when click on crystaldecisions.crystalreports.engine.dll then click 'OK'

    system will prompt out that

    A reference to 'C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\crystaldecisions.crystalreports.engine.dll" count not be added.Please make sure that the tile is accessible,and that it is a valid assembly or COM component.

    Please help.

    Thank you.

  • I copied the four crystal reports dll files into both "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies" and "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies" as that is what I have. I just checked my reference and it points to the folder "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies", try that...

    I will see if I can go thru my steps on another pc and let you know if I find something I might have missed.

  • Hi Richard,

    Thank you for your reply.I already try it at my place.Unfortunaly stil cant.

    My pc just got

    C:\Program Files\Microsoft SQL Server\90 no SDK\Assemblies folder at my folder so I add SDK folder and Assemblies folder.

    Then I copy one of the crystal report's dll file into that new folder .

    Then I go to ssisscript intergation service script task add this as new reference from C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies but system not allow me to add this reference.

    Below is the msg.

    'A reference to 'C\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ctystaldecisions.crystalreports.engine.dll' count not be added.Please made sure that the file is accessible, and that it is a valid assembly or COM component.

    Please Help.

    Thank you.

  • try installing Crystal Report Framework For .Net 2.0

    C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports\CRRedist2005_x86.msi

    If not found, google it and download it.

    see if this helps

  • Thank you for this it saved me alot of work and headache

  • Hi Richard Cranston

    I am new to this SSIS. I need to create some .rtf file using Crystal report from the SSIS package. I saw the post that you already Used the ssis package to genrate a report using Crystal report. In my workplace, I am getting a new project where I have to do this with in a very short time. Our database server is SQl 2005. COuld yopu please help me by providing an SSIS package example and steps on how to do it. I would be so grate ful to you. Thanks in advance.

    Runa,

Viewing 15 posts - 1 through 15 (of 35 total)

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