January 23, 2012 at 3:44 pm
Hi there,
Nice article, it got me most of the way to running a Crystal Report with parameters from SSIS. Just a few points to note that tripped me up along the way.
1. In the example given the parameter names were not prefixed with the @ symbol. For example for a Crystal Report with a parameter name of "DHB" the following line would not work:
cr.SetParameterValue("DHB", Dts.Variables("User::DHB").Value)
... where as the following line would ...
cr.SetParameterValue("@DHB", Dts.Variables("User::DHB").Value)
Might be something to do with versioning, who knows. Edit - Actually found this out. My parameters were created as a result of stored procedure parameters, they are always prefixed with an @ symbol. Regular parameters don't have that @ symbol.
2. This works great for Crystal Reports that query data directly from tables however not Crystal Reports that get data from stored procedures. I've not figured out the stored procedure bit yet but I'm still working on it. It looks as though it goes through all the tables in the Crystal Report and applies the log in information however I don't see any options to make it do the same with stored procedures. If anyone has any thoughts love to hear them.
Edit - Actually the above is not the case. It seems (at least in my environment) that any report with a parameter will fail with an error something like "Database Vendor Code 17". So I can run simple reports which don't have parameters on both tables and stored procedures but as soon as I put a parameter in I get the following error:
Error: 0x1 at Export Otago Data: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> CrystalDecisions.CrystalReports.Engine.InternalException: Failed to open the connection.
Details: [Database Vendor Code: 17 ]
Failed to open the connection.
3. Make sure the versions of your DLL's are the same. If you mix up the DLL versions on the files your code may not compile. For example I have multiple copies of those files all with different version numbers: 12.1.1100.882, 12.1.2000.882, 12.2.1100.290 and 12.3.2000.601. That said three of my files are on the latest version and the CrystalDecisions.ReportSource.dll is on 12.2.1100.290 as I don't have a 12.3 version of it. Now it seems to work however when I've tried different file version combinations it complains.
4. In the supplied code where you specify the log in credentials there might be a mistake, it says the following:
LogonToDatabase(cr.Database.Tables, "MySqlServerName", "MyDatabaseName", "MyPassword")
... However the subroutine is actually expecting the following ...
LogonToDatabase(cr.Database.Tables, "MySqlServerName", "MyUserName", "MyPassword")
Note that it's not asking for the database name as this is already in the report, it needs the user name to go with the password.
5. If you are passing parameters don't forget to specify in the "Script Task Editor" dialogue box (you know, the one where you choose the language you want to write your script in) the variables in you SSIS package that your script will have access to. Simple mistake on my part but had me thinking for a couple of minutes.
Think that's about it. Again thanks for the original post, this is by far the best post on this that I've found.
Kind regards,
Chris Rutherford
January 24, 2012 at 3:26 pm
Hi there,
We'll I've done some more investigation and have used the CHM help file provided by SAP Business Objects called Crystal Reports 2008 .NET SDK.chm (http://help.sap.com/businessobject/product_guides/boexir31/en/crsdk_net_apiRef_12_en.chm)
I only needed to add in two references and they were:
CrystalDecisions.CrystalReports.Engine.dll (version 12.3.2005.925)
CrystalDecisions.Shared.dll (version 12.3.2005.925)
The code below takes a few hard coded parameters and exports a Crystal Report to c:\report1.xls formatted in Excel Data Only format. You can change the export type to most things Crystal Reports supports, check out the ExportReport subroutine for the code. I've only coded to allow Excel Data Only (i.e. ExcelRecord), Excel, PDF and Word as we use those most here.
Before I move this to PROD I'll create SSIS variables and just pass them to the procedure.
The report has one parameter but if your report does not just comment out the call to the SetReportParameters subroutine.
One thing that tripped me up a lot was the fact that I'm used to specifying server names that have SQL Server instances with square brackets around them, i.e. [ServerName\Instance]. If your data is on an SQL instance don't bother with the square brackets as the report will just time out with Database Vendor Code 17.
Think that's everything but if anyone in future is reading this and gets stuck or has a question please let me know.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
<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 = "C:\Report1.rpt"
Dim exportFormat As String = "ExcelRecord"
Dim myConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo()
myConnectionInfo.ServerName = "MyServer"
myConnectionInfo.DatabaseName = "MyDatabase"
myConnectionInfo.UserID = "MyUserID"
myConnectionInfo.Password = "MyPassword"
' Load the report.
myReportdocument.Load(reportPath)
SetReportCredentials(myConnectionInfo, myReportDocument)
SetReportParameters(myReportDocument)
ExportReport(myReportDocument, "C:\Report1.xls", 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("ParameterName", "ParameterValue")
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()
myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
myExportOptions.ExportDestinationOptions = myDiskFileDestinationOptions
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 = "Word" Then
myExportOptions.ExportFormatType = ExportFormatType.WordForWindows
End If
myExportOptions.ExportFormatOptions = Nothing
myReportdocument.Export(myExportOptions)
End Sub
End Class
Kind regards,
Chris
January 24, 2012 at 7:50 pm
Glad this post help get you started...and I appreciate the feed back and your posting of your results....I am sure it will help me and others.
Nice job investigating passing parms & using stored procedures; obviously my reports where much simplier.
January 24, 2012 at 8:44 pm
Hey there,
Cheers for the reply. I do have one thing I'm stuck on though. I've Crystal Reports 2008 installed on my local machine so of course I did all the development on my machine. It all works very well, the reports run, export to various formats etc.
Now the production machine does not have anything of Crystal Reports installed on it. So I figured I'd just copy the two DLL's to the same place on the production server (my machine is 64 bit as well as the production server) and then all should be good.
Unfortunately that's not the case. When I moved it to PROD it started complaining that it could not find the file or assembly CrystalDecisions.ReportAppServer.CommLayer.dll. So I copied that one across too and then re-ran things.
Next it popped up saying:
"Please install the appropriate Crystal Reports redistributable (CRRedist*.msi) that contains the version of the Crystal Reports runtime (x86, x64, or Itanium) that is required."
I don't have that particular installation file and I don't want to have to install Crystal Reports 2008 on our production server.
Anyone have any experience of releasing something like this to a system with no Crystal Reports stuff on it in the first place? Likely this is not the forum for this so I'll put in a ticket with SAP Business Objects and let you know how I get on just in case anyone else has something similar.
Kind regards,
Chris
January 25, 2012 at 6:55 am
I was fortunate to have my Prod server also run Crystal Report Server. Maybe you can install just the Cystal Report Viewer?
January 25, 2012 at 7:03 am
Do you have any blog/articel where you have put this as an exmaple /study material for others...
Or any link you know to get help to do this stuff..
Thanks.
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 26, 2012 at 6:48 pm
Hi there,
Okay figured out how to get these reports to export on a machine using SSIS that does not have any Crystal Reports components already on it. I'm using Crystal Reports 2008 with Visual Studio 2008 (BIDS). Thanks for the suggestion Richard about trying to install the CR Viewer, unfortunately that didn’t work, nice thought though, I’d hoped it would work.
You need to download and install the Crystal Reports 2008 Runtime Package for the .NET Framework (EXE file). I found it at the following location:
https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm
It's only a 25 MB file, once you unzip it you will find the file is called CRRuntime_12_0_mlb.exe. If you are using anything higher than IE 7 with that URL up there you will likely have no option to download anything. You'll need IE 7, Mozilla or Firefox (I had to get a colleague to download it as he was running Firefox). If you don't like that link you can try the following:
http://www.sdn.sap.com/irj/boc/downloads
... and it will take you to the screen before which tells you what browsers are supported. From there you can click on "Get SAP Crystal Solutions downloads" then choose "Crystal Reports" from the Software Product drop down box then "Crystal Reports for VS .NET 2008" (well that's the version I'm using). When you expand the results to 20 rows per page the one I used was fourth from the bottom.
Just run this on the machine with no Crystal Reports components and your SSIS packages which call those two DLL's I referred to in an earlier post work fine. FYI, you will need a valid Crystal Reports 2008 product code to install this. There is one known bug in that you may get an error when trying to export to either Excel or Rich Text formats. If you get an error when exporting to those formats but you can export to PDF you need to copy the file from the following location:
C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\libpng10.dll
... and put it in a location that is specified in your server PATH statement. For example I just put it in the Windows folder and everything worked. You can read more about this particular error in the following link:
http://forums.sdn.sap.com/thread.jspa?threadID=1065075&start=0&tstart=0
They said there was a fix pack for it but it's 202 MB to download. Seems odd to me that the original installation was only 25 MB and the fix pack is 202 MB! So I just opted to copy the file. Alternatively you can just put the location where the file resides into your PATH statement however that will require a restart.
If you get an error saying that the Crystal Reports redistributable was not installed correctly or you don’t have permission make sure you’re running your SSIS package in 32 bit mode.
I'm not a Crystal Reports 2008 specialist at all which would explain why it's taken me four days to get this thing to work. So if any other SQL Server type folks need to do the same hopefully I've saved you some pain.
Kind regards,
Chris Rutherford
January 27, 2012 at 7:06 am
Thanks Chris again for you investigation and posting your results...I am sure it will help anyone doing this. I know I will refer to this when I have to configure a new server and get this running again.....I did go thru earlier entries in this post and noticed I had stated to install Crystal Report Framework For .Net 2.0 at this folder location or at least download it.
C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports\CRRedist2005_x86.msi
Glad you found the website to download newer versions accordingly Crystal Reports 2008 Runtime Package for the .NET Framework
I already downloaded it just to have it ready.....Thanks again, Rich
March 13, 2012 at 2:27 pm
Chris Rutherford (1/24/2012)
Hey there,Cheers for the reply. I do have one thing I'm stuck on though. I've Crystal Reports 2008 installed on my local machine so of course I did all the development on my machine. It all works very well, the reports run, export to various formats etc.
Now the production machine does not have anything of Crystal Reports installed on it. So I figured I'd just copy the two DLL's to the same place on the production server (my machine is 64 bit as well as the production server) and then all should be good.
Unfortunately that's not the case. When I moved it to PROD it started complaining that it could not find the file or assembly CrystalDecisions.ReportAppServer.CommLayer.dll. So I copied that one across too and then re-ran things.
Next it popped up saying:
"Please install the appropriate Crystal Reports redistributable (CRRedist*.msi) that contains the version of the Crystal Reports runtime (x86, x64, or Itanium) that is required."
I don't have that particular installation file and I don't want to have to install Crystal Reports 2008 on our production server.
Anyone have any experience of releasing something like this to a system with no Crystal Reports stuff on it in the first place? Likely this is not the forum for this so I'll put in a ticket with SAP Business Objects and let you know how I get on just in case anyone else has something similar.
Kind regards,
Chris
I'm getting the same error on a server that has Crystal Reports 2008 on it, so why would I get this same error when it it actually installed? weird!
March 13, 2012 at 2:47 pm
Hi there,
Odd, however I might comment that perhaps your installation does not have the Runtime Package for the .NET Framework (maybe it was not installed by default if that's possible). Again I'm no Crystal Reports expert but I know the steps in my quote got it working for me. As always test on a development box first as in the past when I've installed parts of Crystal Reports onto a box with Crystal Reports already installed I remember experiencing pain.
Chris Rutherford (1/26/2012)
You need to download and install the Crystal Reports 2008 Runtime Package for the .NET Framework (EXE file). I found it at the following location:https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm
It's only a 25 MB file, once you unzip it you will find the file is called CRRuntime_12_0_mlb.exe. If you are using anything higher than IE 7 with that URL up there you will likely have no option to download anything. You'll need IE 7, Mozilla or Firefox (I had to get a colleague to download it as he was running Firefox). If you don't like that link you can try the following:
http://www.sdn.sap.com/irj/boc/downloads
... and it will take you to the screen before which tells you what browsers are supported. From there you can click on "Get SAP Crystal Solutions downloads" then choose "Crystal Reports" from the Software Product drop down box then "Crystal Reports for VS .NET 2008" (well that's the version I'm using). When you expand the results to 20 rows per page the one I used was fourth from the bottom.
Just run this on the machine with no Crystal Reports components and your SSIS packages which call those two DLL's I referred to in an earlier post work fine. FYI, you will need a valid Crystal Reports 2008 product code to install this. There is one known bug in that you may get an error when trying to export to either Excel or Rich Text formats. If you get an error when exporting to those formats but you can export to PDF you need to copy the file from the following location:
C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\libpng10.dll
... and put it in a location that is specified in your server PATH statement. For example I just put it in the Windows folder and everything worked. You can read more about this particular error in the following link:
http://forums.sdn.sap.com/thread.jspa?threadID=1065075&start=0&tstart=0
They said there was a fix pack for it but it's 202 MB to download. Seems odd to me that the original installation was only 25 MB and the fix pack is 202 MB! So I just opted to copy the file. Alternatively you can just put the location where the file resides into your PATH statement however that will require a restart.
If you get an error saying that the Crystal Reports redistributable was not installed correctly or you don’t have permission make sure you’re running your SSIS package in 32 bit mode.
Kind regards,
Chris
March 13, 2012 at 3:13 pm
ok, I totally didn't see the "check to make sure you are running the package in 32bit mode". How do I check this?
March 13, 2012 at 3:22 pm
Hi there,
We run the SSIS package via SQL Server Agent as a scheduled job. So in there you create the step to run the SSIS package. As part of that configuration there is an Execution Options tab in the Job Step properties. In that Execution Options tab there is a tick box that says "Use 32 bit runtime".
Just make sure that is ticked and your SSIS package will be running in 32 bit mode.
If however you've not yet put it in a SQL Server Agent Job and you are running it interactively using BIDS then under the Project menu in BIDS the last option will be the properties of the project. Click on that then on the Debugging area of the Configuration Properties you should see a Debug Option called Run64BitRuntime, make sure that is False.
Hope that helps.
Kind regards,
Chris
March 13, 2012 at 4:21 pm
Perfect! Thanks. I still have some coding errors, but I think I will survive from this point. This has been very helpful!
March 14, 2012 at 6:23 am
HI,
I created a crystal report with two text boxes and one picture and i Exported it in the design time to xml.now i want to read the same xml and I want to create the same template in crystal report with two text boxes and one picture controls and if it is possible i want to change the height and the width of the controls also.if anybody is having any idea please share and try to solve my issue
March 14, 2012 at 12:33 pm
Is it possible to use integrated security with the connection properties?
Edit: YES it's possible..
myConnectionInfo.IntegratedSecurity = True
myConnectionInfo.ServerName = "servername"
myConnectionInfo.DatabaseName = "databasename"
'myConnectionInfo.UserID = "userid"
'myConnectionInfo.Password = "password"
It's working perfect!!! Love it! Thanks!
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply