SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need to create ssis package to run crystal report


need to create ssis package to run crystal report

Author
Message
Richard Cranston
Richard Cranston
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 158
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.



Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10118 Visits: 5314
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
CozyRoc
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2402 Visits: 2235
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18591 Visits: 20443
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Richard Cranston
Richard Cranston
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 158
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
Richard Cranston
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 158
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
Penny Kenney
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 83
I need to do this too...will you share any secrets
Richard Cranston
Richard Cranston
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 158
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:Tonguelant").Value)
cr.SetParameterValue("Division", Dts.Variables("User:BigGrinivision").Value)
cr.SetParameterValue("FiscalYear", Dts.Variables("User::FiscalYear").Value)
cr.SetParameterValue("FiscalMonth", Dts.Variables("User::FiscalMonth").Value)
cr.SetParameterValue("Dimension", Dts.Variables("User:BigGrinimension").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.



Attachments
vbnet_win_dbengine.zip (149 views, 12.00 KB)
vbnet_win_exporttopdf.zip (137 views, 55.00 KB)
Penny Kenney
Penny Kenney
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 83
Thanks...can't wait to give it a try!
eankl
eankl
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search