need to create ssis package to run crystal report

Question

need to create ssis package to run crystal report

Richard Cranston

Hall of Fame

Points: 3680
More actions
October 28, 2009 at 1:10 pm

#227549

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.

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

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/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 4

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 5

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.

Penny Kenney Old Hand Points: 381 More actions · Answer 6

Penny Kenney

Old Hand

Points: 381

November 13, 2009 at 3:47 pm

#1079293

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

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 7

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)

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.

Penny Kenney Old Hand Points: 381 More actions · Answer 8

Penny Kenney

Old Hand

Points: 381

November 16, 2009 at 10:04 am

#1079779

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

eankl SSC Enthusiast Points: 167 More actions · Answer 9

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 10

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.

eankl SSC Enthusiast Points: 167 More actions · Answer 11

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.

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 12

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

srchan SSC Journeyman Points: 88 More actions · Answer 13

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

jbegum Valued Member Points: 59 More actions · Answer 14

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,