need to create ssis package to run crystal report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » need to create ssis package to run crystal...

34 posts, Page 1 of 4

1 2 3 4 »»»

need to create ssis package to run crystal report

Rate Topic

Display Mode

Topic Options

Author

Message

Richard Cranston

Posted Wednesday, October 28, 2009 1:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:09 AM
Points: 138, Visits: 124

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

Elliott Whitlow

Posted Wednesday, October 28, 2009 2:22 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

CozyRoc

Posted Wednesday, October 28, 2009 8:58 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,106, Visits: 2,111

Richard Cranston (10/28/2009)

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

Phil Parkin

Posted Thursday, October 29, 2009 1:11 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #810537

Richard Cranston

Posted Thursday, October 29, 2009 7:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:09 AM
Points: 138, Visits: 124

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

Richard Cranston

Posted Tuesday, November 03, 2009 10:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:09 AM
Points: 138, Visits: 124

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

Penny Kenney

Posted Friday, November 13, 2009 3:47 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:41 AM
Points: 13, Visits: 65

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

Post #818918

Richard Cranston

Posted Monday, November 16, 2009 8:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:09 AM
Points: 138, Visits: 124

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 (94 views, 12.32 KB)

vbnet_win_exporttopdf.zip (79 views, 55.31 KB)

Post #819421

Penny Kenney

Posted Monday, November 16, 2009 10:04 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:41 AM
Points: 13, Visits: 65

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

Post #819502

eankl

Posted Thursday, April 08, 2010 8:59 PM

Forum 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 »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions