Anaplan Connect: Difficulties setting up export from Anaplan to SQL Server

LukasRooseleers

Hi,

We're trying to set up a connection to export data from Anaplan to Microsoft SQL Server.

We already have a working connection to import data into Anaplan, but we can't manage to set this up the other way around (export).

We set up the export connection using the Anaplan Connect guide and our working import actions.

We run the trigger the action using windows task scheduler. See error log attached to this question. Seems like there is a problem with the jdbc connection. What do the error codes mean?

Error observed : com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)

The index 1 is out of range.

Anybody who had similar problems or tips how to solve this?

Thanks in advance for helping!

Stacey_Gibbens

Hi Lucas,

When I was actively using AnaplanConnect, I was able to query directly from SQL servers, but anything going back TO a database needed to be staged as a tab delimited .txt and run through an ETL tool via a scheduler to import. I would be very interested if this actually were bidirectional. Please let us know if you actually do determine a way to integrate into a SQL table without staging that file export on a share.

I wonder if the Anaplan API can do this…. because of the additional layer of postman (or whatever) it may be that this can be made bidirectional without staging a file… Hm. Deep thoughts. :)

You might also find this blog useful. I wrote it eons ago, but seems applicable to the things you're working on.

https://community.anaplan.com/discussion/67536/tips-for-large-script-implementations-using-anaplanconnect-1-4-x

Stacey

QuinE

Hi @LukasR -

First off, I would highly recommend upgrading to Anaplan Connect 4.2.1. To get going with it quickly, please look at this article. Additionally, please note that your version of Anaplan Connect is no longer supported. As such, if you file a ticket, Anaplan will not be able to help.

As for direct integration to SQL Server, it is easy to set up with Anaplan Connect 4.2.1. You will need a jdbc.prroperties file similar to this:

jdbc.query=INSERT INTO SAMPLE_DB.dbo.export_single (Line_Item, Version, Product, Market, Period, Value) VALUES (?, ?, ?, ?, ?, ?)

jdbc.username=YOUR_USER

jdbc.password=YOUR_PASSWORD

jdbc.fetch.size=10000

jdbc.connect.url=jdbc:sqlserver://qeddy-windows-11:1433;databasename=SAMPLE_DB;encrypt=false;statementPoolingCacheSize=10;disableStatementPooling=false;enablePrepareOnFirstPreparedStatementCall=true;

Note the jdbc.query line has specific named columns to insert, and in the same statement, there is an equivalent number of ? characters. This is very important. If you do not have a matching ? for each column, then you will get an Index 1 is out of range error (as you have in your example).

Stacey_Gibbens

@QuinE

That is so cool! I wasn't aware of this update to AnaplanConnect!! :) :) :)

Thank you for letting us all know!

Stacey

LukasRooseleers

Hi @QuinE,

Thanks for the advice on the jdbc.query. Will definitely keep in mind!

I'm now trying to update Anaplan Connect to the newest version. I followed instructions in the article you refered to. Everything went fine until the last step. I set up the java file path in the Anaplan Client bat file but the error "java.exe was unexpected at this time". Any ideas how to solve this?

Thanks for helping!

Lukas

QuinE

Hi @LukasRooseleers - based on your screenshot, I am unclear what you are trying to do. In the file AnaplanClient.bat, you need to provide the explicit path to where java.exe is located. Here is an example from my Windows environment:

Once you have this update, then you can simply call AnaplanClient.bat to test the installation.

LukasRooseleers

Super, thanks @QuinE ! I can't call AnaplanClient.bat yet, waiting for it to get whitelisted by security. Then I should be fine :)

QuinE

Sounds great and good luck!

LukasRooseleers

Thanks @QuinE !

I also managed to get the export running. Had to use square brackets in the SQL query for the columns that had spaces. This fixed the 'Index 1 is out of range' error.

Then I tried to make another export connection. Same model (so same Workspace ID, ModelID, User) , same database, jdbc url, user, password. I created a new table in SQL Server and made a new jdbc query:

Any idea why connection to the database is failing? I notice that my jdbc query is not present in the properties file but it's written in the same way as the first one which is working fine.

Thanks again for helping,

Lukas

QuinE

Hi @LukasR - The [ and ] are required around column names that have spaces and other special characters. In your example, I see that your columns have spaces.

As for your other issue, I suspect it is a permissions issue. From what you say, everything is identical except a change in target. Please carefully walk through your permissions and apply scientific methodology (only change one variable at a time and retest) when trying to troubleshoot.