Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

need to create ssis package to run crystal report Expand / Collapse
Author
Message
Posted Wednesday, October 28, 2009 1:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:34 AM
Points: 146, Visits: 139
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.


Post #810249
Posted Wednesday, October 28, 2009 2:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #810310
Posted Wednesday, October 28, 2009 8:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 1,114, Visits: 2,191
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/

Post #810465
Posted Thursday, October 29, 2009 1:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 4,828, Visits: 11,179
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.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #810537
Posted Thursday, October 29, 2009 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:34 AM
Points: 146, Visits: 139
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.


Post #810721
Posted Tuesday, November 03, 2009 10:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:34 AM
Points: 146, Visits: 139
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.


Post #813080
Posted Friday, November 13, 2009 3:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 06, 2013 1:55 PM
Points: 13, Visits: 71
I need to do this too...will you share any secrets
Post #818918
Posted Monday, November 16, 2009 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:34 AM
Points: 146, Visits: 139
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.






  Post Attachments 
vbnet_win_dbengine.zip (102 views, 12.32 KB)
vbnet_win_exporttopdf.zip (87 views, 55.31 KB)
Post #819421
Posted Monday, November 16, 2009 10:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 06, 2013 1:55 PM
Points: 13, Visits: 71
Thanks...can't wait to give it a try!
Post #819502
Posted Thursday, April 08, 2010 8:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 20, 2010 1:22 AM
Points: 3, Visits: 6
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.

Post #900134
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse