Blog: An automated solution for Crystal Reports conversion from Oracle to SQL Server

One of our customers decided to modernize their Oracle database to Microsoft SQL Server. However, the thousands of SAP Crystal Reports connected to their existing Oracle database presented a huge challenge to their migration effort. They reached out to DB Best to develop an automated way to convert all of the connection information from Oracle to SQL Server and, more importantly, develop an automated solution that can extract the Oracle PL/SQL statements in the reports, convert them to SQL Server Transact-SQL, and insert the tested T-SQL commands back into the original report. In this blog post, we cover how we were able to pull this off for our customer. If you have Crystal Reports and are looking to migrate from one database platform to another, check out how the automation tools that we developed for our customer can significantly reduce the time and risk for your Crystal Reports conversion projects.

Technical background

The picture below describes the original customer’s system.

Our customer has two types of Crystal Reports:

• Data table objects. These reports don’t include any SQL statements. All you need to do is reconnect the data source from Oracle to SQL Server.
• SQL command objects. These reports include embedded SQL statements. So, these statements should also be converted to SQL Server.

The typical workflow that allows for the conversion of Crystal Reports with embedded SQL statements is shown below.

• Extract Oracle-compatible PL-SQL code from Crystal Report.
• Convert PL-SQL code to T-SQL use Microsoft SQL Server Migration Assistant (SSMA). This makes the SQL code compatible with Microsoft SQL Server.
• Validate conversion, running both PL-SQL and T-SQL code against Oracle and SQL Server databases respectively and compare the results.
• Update T-SQL code, if needed (if the compared reports on the previous step have any differences).
• Insert T-SQL code into the Crystal Report, providing SQL Server connection credentials.

The biggest customer issue is that they don’t have an environment where they can do these operations in bulk. DB Best provided the customer with a full automation of all of these steps in order to achieve scale and speed of database and reports migration.

DB Best solution

The DB Best team was able to leverage their extensive knowledge of database migrations which enabled them to come up with a tested solution to automate the migration process. We used in-house and Microsoft tools to achieve this goal.

• First, we created the DB Best Crystal Reports Extractor Tool (CRET) that extracts SQL statements from Crystal Reports using the Crystal Reports .NET SDK.
• Then we automated PL-SQL to T-SQL conversion process through the SQL Server Migration Assistant (SSMA) console.
• For the next step, we used DB Best Migration Platform. This utility allows for test creation and validation of the converted code, automatically comparing the results and highlighting the differences.
• Finally, we use the new DB Best CRET to swap out the previous Oracle PL/SQL statement with the new fully-tested SQL Server Transact-SQL version for the report.

All these processes run in the console, with no need to use the Crystal Reports designer. This means that we just need to launch a script to complete the conversion. However, we also need to check the error log file to manually update the T-SQL code in cases where either SSMA could not completely convert or the automated test results from the DB Best Migration Platform failed to produce the same result.

Please check out our video that describes the general approach to Crystal Reports conversion during an Oracle to SQL Server database migration.

Customer benefits

With our fully automated system for migration of thousands of Crystal Reports from Oracle to SQL Server, our customer saved thousands of hours of work. Moreover, the automated solution provides higher quality result by avoiding the human factor for the vast majority of reports. Our customer managed to meet strict database migration deadlines, as their brand new SQL Server database system went live into production with minimal downtime.

If you’re considering a full-scale migration project, or need help automating some steps along the way, talk to DB Best. We will provide the expert team to help you get through the hoops.