Wednesday, March 31, 2021

EPM 11.2, Visual Studio, and SSIS Packages

This is a bit of a weird one, and it isn't Oracle's fault at all.  This is actually a Microsoft issue.

Suppose you are migrating from Hyperion / Oracle EPM 11.1.2.4 to EPM 11.2.x.  Further suppose you have custom data integrations in FDMEE/ODI or MaxL that rely upon SSIS (SQL Server Integration Services) automation.

Your EPM 11.2.x system would be using MS SQL Server 2016 SP2+ if you want to use SSIS.  I checked the certification matrix last week and Oracle still hasn't certified MSSQL 2019 for EPM 11.2.x.

(Disclaimer: the above paragraph may become be dated as Oracle might certified MSSQL 2019 in the future. The Internet is forever!)

Now here's the fun part.

To migrate your SSIS packages to MSSQL 2016, you will need to first import and upgrade them via MS Visual Studio.  The version available to download is Visual Studio 2019.

Once upgraded, you use MS SQL Management Console to import them into SSIS.  The version of this software as of this writing is 18.8.

This combination of software do not play well with each other, and you'll get an error message in MS SQL Console when you try to work with the upgraded packages.  This exact error and solution has been written about on many blogs.  I'll just jump straight to the solution.

You'll need to uninstall MS SQL Management Console 18.8, and then grab version 16.x from Microsoft's download page.  There's a link there that lets you download older versions, and 16.x is still available as of this writing.  Version 17.x does not solve our issue... we have to go all the way down to 16.x.

You may then complete your migration in MS SQL Console 16.x.

One final tip: if your custom integrations are using Microsoft's dtexec utility, you'll need to replace the reference in the script's path to dtexec from 110 (SQL Server 2012) to 130 (SQL Server 2016).

1 comment:

  1. Thanks for the info. A customer with a 8000 GB HFM in SQLServer 2012 and is going to upgrade. Good info to know on the SQLServer.

    ReplyDelete

Thank you very much for your interest in this blog! I hope you're finding it helpful.

Please keep comments relevant to the topic in the post, as this blog is not a free-for-all substitute for Oracle Support or traditional consulting. If you have many questions unrelated to the specific topic at hand, consider contacting me on LinkedIn (https://www.linkedin.com/in/daveshay) so we may discuss the possibility of consulting.

Commenting on posts older than 90 days unfortunately goes into moderation, thanks to spammers who've been hitting this blog. Please have patience, and thanks for your understanding!

Comments including URLs linking back to gambling or other things unrelated to Oracle EPM will be deleted on sight. If you're an EPM consultant and are offering me constructive criticism or a tip, go ahead and DO link back to your blog or firm's website if you so desire.

Thanks again for reading!