March 16, 2012 at 8:14 am
Have you experienced where the package takes a long time to run the report? I ran it from our in house application which does the same thing as the package accept runs only 1 report and it's very fast, but when I run it from SSIS it's taking forever. Any thoughts on how to speed up the package?
edit: Indexes needed rebuilding. 🙁
March 16, 2012 at 2:16 pm
Does anyone know how to create an editable word doc from the CrystalDecisions.Shared DLL? I have tried the following but can't seem to get rid of the "Invalid DLL or export type" error in my package. If I strictly do word format it works fine, but the users want to be able to edit the word doc without having to manipulate text boxes.
I'm going crazy with trying to figure it out!!
This is the contents of my script task:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports CrystalDecisions.CrystalReports
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Windows.Forms.CrystalReportViewer
Imports CrystalDecisions.CrystalReports.Engine
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim myReportDocument As ReportDocument = New ReportDocument()
Dim reportPath As String = "ThisReport.rpt"
Dim exportFormat As String = "Word"
Dim myConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo()
Dim formattedDate As String
formattedDate = Date.Today.ToString("yyyy/MM/dd HH:mm:ss")
myConnectionInfo.IntegratedSecurity = True
myConnectionInfo.ServerName = "Server"
myConnectionInfo.DatabaseName = "DB"
' Load the report.
myReportdocument.Load(reportPath)
SetReportCredentials(myConnectionInfo, myReportDocument)
SetReportParameters(myReportDocument)
ExportReport(myReportDocument, "UNC\ReportName.doc", exportFormat)
Dts.TaskResult = ScriptResults.Success
End Sub
Private Sub SetReportCredentials(ByVal myConnectionInfo As CrystalDecisions.Shared.ConnectionInfo, ByVal myReportDocument As ReportDocument)
Dim myTables As Tables = myReportdocument.Database.Tables
For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
Next
End Sub
Private Sub SetReportParameters(ByVal myReportDocument As ReportDocument)
myReportdocument.SetParameterValue("@p1", Dts.Variables("User::p1").Value)
End Sub
Private Sub ExportReport(ByVal myReportDocument As ReportDocument, ByVal exportFilePath As String, ByVal exportFormat As String)
Dim myDiskFileDestinationOptions As New DiskFileDestinationOptions()
myDiskFileDestinationOptions.DiskFileName = exportFilePath
Dim myExportOptions As New ExportOptions()
Dim myFormatOptions = ExportOptions.CreatePdfRtfWordFormatOptions()
myFormatOptions.FirstPageNumber = 1
myFormatOptions.LastPageNumber = 10
myExportOptions.ExportFormatOptions = myFormatOptions
myExportOptions.ExportFormatType = ExportFormatType.EditableRTF
myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
myExportOptions.ExportDestinationOptions = myDiskFileDestinationOptions
/*commented out the following to try what i did above*/
'If exportFormat = "ExcelRecord" Then
' myExportOptions.ExportFormatType = ExportFormatType.ExcelRecord
'ElseIf exportFormat = "Excel" Then
' myExportOptions.ExportFormatType = ExportFormatType.Excel
'ElseIf exportFormat = "PDF" Then
' myExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
'ElseIf exportFormat = "RTF" Then '= "Word" Then
' myExportOptions.ExportFormatType = ExportFormatType.NoFormat
'End If
myExportOptions.ExportFormatOptions = Nothing
myReportdocument.Export(myExportOptions)
End Sub
End Class
April 3, 2012 at 8:28 am
So after weeks of agonizing pain of getting the error, "Invalid DLL or Export Type" when trying to use the "EditableRTF" option in the script task. Our deveolopers finally figured out that the PATH environment variable as to edited and the following has to occur:
When referencing Crystal Reports DLL's to use in an SSIS package, you must do several things including:
1.) Whichever Crystal DLL's you're using, for them to work properly and not get certain COM errors, you MUST place the appropriate DLL directory as part of the system "PATH" environment variable. i.e. (C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86).
After adding that directory path to the PATH environment variable, the package worked perfectly.
Hope this helps some other person out there dealing with this problem. 🙂
December 13, 2012 at 11:27 am
Add your Crystal Report code in a Script Task Object.
To achieve this you have to add the references to your SSIS project like you do to a regular VB.Net project.
Once in the editor add:
- Under project explorer r-click your project, References, add your Crystal references:
CrystalDecisions.CrystalReports.Engine.dll
CrystalDecisions.ReportSource.dll
CrystalDecisions.Shared.dll
CrystalDecisions.Windows.Forms.dll
Then on your ScriptMain add:
Imports CrystalDecisions.CrystalReports
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Windows.Forms.CrystalReportViewer
Then program as usual:
Public Sub MainAS()
Dim CrystalReportViewer1 As New CrystalDecisions.Windows.Forms.CrystalReportViewer
.....
.....
End Sub
Presto!!
February 19, 2015 at 2:23 pm
thanks a lot for the details!!! it really helped me out!!!
October 25, 2022 at 6:03 am
Hi,
Can anyone help me by giving me an example .dtsx package that can load a crystal report and execute from SSIS?
I needed to execute a crystal report that can accept a single parameter several times(over 150 times can fo a for loop) and save the output in pdf format.
Appreciate any help.
Thanks
J
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply