Anaplan - Export via jdbc

seshagiri · May 2020

Hi Team ,

As i am planning to Export data into sqlserver table i am getting the below error can any one please suggest me whats went wrong .

Export Code below

---------------------------------

@Echo off
rem For Windows OS : This example explains the usage of basic Authentication with Anaplan Connect.
rem It also loads a source text file and runs an Anaplan import into a module.
rem For details of how to configure this script visit https://help.anaplan.com/anapedia/Content/Downloads/DL_Downloads.html

set AnaplanUser="Username:Password"
set WorkspaceId="My Workspace"
set ModelId="My Model"

set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"

rem *** to continue the command to the next line put ^ at the end of the line. No spaces after the ^.

set Operation=-maxretrycount 5 -retrytimeout 30 ^
-export "Export Action" -execute -jdbcproperties "C:\jdbc_insert_sqlserver.properties"

rem *** End of settings - Do not edit below this line ***

setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% %Credentials% %Operation%
@Echo %Command%
cmd /c %Command%
pause

JDBC Insert property code

--------------------------------------

# JDBC Connection string (Oracle, Mysql, H2, etc.)
jdbc.connect.url=jdbc:sqlserver://servername:port;databaseName=dbname

# JDBC login username
jdbc.username=user

#JDBC login password
jdbc.password=password

# Use INSERT queries to export your data into database
# Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't
# duplicate an existing record, then SQL inserts it as usual. If the record is a
# duplicate, then the IGNORE keyword tells SQL to discard it silently without generating an error.
# Use the REPLACE command rather than the INSERT command. If the record is new, it is inserted
# just as with INSERT. If it is a duplicate, the new record replaces the old one.
# SQL query example
jdbc.query=insert into anaplan.dbo.py_sales (REGION, PY_SALES) values (?,?)

# -----NEW PARAMETER----
# JDBC parameters for parametrized SQL write in {jdbc.query} above.
# NOTE: The number of parameters must be equal to the number of parameters provided in the {jdbc.query} above.
# The parameter names must match column names from Anaplan export action.
# The JDBC parameters below will be mapped starting from left to right to the '?' in {jdbc.query} above.
# In this example, the exported data has columns names column1 and column2. These will be mapped
# starting from left to right to each of the '?' above.
jdbc.params="Sales Region","PY_Sales"

the above values are not original just i changed . please find the error which i am getting it . please help me

Feign: status 500 reading AnaplanAPI#getExport(String,String,String); content:
{"status":{"code":500,"message":"Internal Server Error"},"path":"/2/0/workspaces/8a81b09b5ef8a64201/models/B65FB1B0415/exports/116000000565","timestamp":"2020-05-07T12:34:02.722446Z

ABerenguela · May 2020

Hi,

this is happening because of the last release... all my interfaces are broken (through python, mulesoft, and the api to power bi)

seshagiri · May 2020

what is the solution to over come this issue

ben_speight · May 2020

Are there any rows in the view you are exporting? There is a long-standing bug that is triggered when there are no rows.

seshagiri · May 2020

yes we have around 5 to 6 rows are there in exporting file

ben_speight · May 2020

So you ran the same export as the same user etc but to a file and there were data (not header) rows?

Are there any filters, sorting, and/or nesting on the rows axis? Does it change if you remove one?

ABerenguela · May 2020

Hi,

also check if you have duplicated values:

try the insert with IGNORE

Regards

seshagiri · May 2020

i have change the export file now i am getting different error message please help me .

2020-05-11 19:14:42 INFO 18268 |-- Initializing Service...
2020-05-11 19:14:42 INFO 18268 |-- Authenticating via Basic...
2020-05-11 19:14:48 INFO 18268 |-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
2020-05-11 19:14:48 INFO 18268 |-- Running Export: EXPORT: Project Impact Assessment - Export (id=116000000563)
2020-05-11 19:14:51 INFO 18268 |-- Run status: Complete. (100.0%)
2020-05-11 19:14:51 INFO 18268 |-- -----------------------------------------
2020-05-11 19:14:51 INFO 18268 |-- <<< The operation was successful >>> 😃
2020-05-11 19:14:51 INFO 18268 |-- - - - - - - - - - - - - - - - - - - - - -
2020-05-11 19:14:51 INFO 18268 |--
2020-05-11 19:14:51 INFO 18268 |-- fileSize - 3013
2020-05-11 19:14:51 INFO 18268 |-- serverAlert - Completed successfully!
2020-05-11 19:14:51 INFO 18268 |-- exportName - EXPORT: Project Impact Assessment - Export
2020-05-11 19:14:56 INFO 18268 |-- Export EXPORT: Project Impact Assessment - Export to database started successfully
2020-05-11 19:14:56 INFO 18268 |-- Attempt 1 : Could not connect to the database! Will retry in 30 seconds
2020-05-11 19:15:26 INFO 18268 |-- Attempt 2 : Could not connect to the database! Will retry in 45 seconds
2020-05-11 19:16:11 INFO 18268 |-- Attempt 3 : Could not connect to the database! Will retry in 60 seconds
2020-05-11 19:17:11 INFO 18268 |-- Attempt 4 : Could not connect to the database! Will retry in 60 seconds

ABerenguela · May 2020

The Anaplan export ran correctly, you need to fix the connection with the Database

2020-05-11 19:14:56 INFO 18268 |-- Attempt 1 : Could not connect to the database! Will retry in 30 seconds

first need to be sure that you can reach it, try an ODBC to check. Also check the access of the SQL user, need to have "connect" and "insert" rights

Regards

seshagiri · May 2020

Hi ABerenguela,
Thanks for providing the reply , the sql user which i am using having the permission to insert the records into Table . To test this one i have inserted a record manually in table . i dont know what i have missed it .
jdbc.connect.url=jdbc:sqlserver://databasename1433;databaseName=Staging
is this statement is correct .
if possible can you share me the property file once

ABerenguela · May 2020

hi,

it is a good idea if you share your configuration, I am using the same as the documentation adapted to my environment:

jdbc.connect.url=jdbc:sqlserver://localhost:49170;dabaseName=My_DB

and in the sql the whole description of the table:

[My_DB].[dbo].[Table_Dim]

ben_speight · May 2020

1433 is the standard port SQL server listens on by default. Either omit that number or separate it from the host using a colon.

Nivetha · May 2020

Hi seshagiri

This is the general syntax for the URL connection.

jdbc.connect.url="jdbc:sqlserver://<server>:<port>;databaseName=testdatabase";

The default port for the SQL server is 1433.

DatabaseName: The path of the database instance.

Here I am listing a few points to note while Exporting to a database using JDBC:

Anaplan Connect property files use commas as delimiters between property values. In order to avoid
problems, Anaplan column names should not have commas.
All target DB table columns should be of a char data type.
A properties file can contain only one query. If you have multiple queries, create a properties file for each
query.
The target database's columns should NOT have a SQL NOT NULL constraint. It’s possible for exported
data to have no data for some columns. In this case, the Anaplan Connect export feature will send NULL
values.
When exporting data from an Anaplan list, the first column of the exported file may have a blank header. If
your first column has a blank header, there is no need to provide the JDBC.params variable in the
properties files. Anaplan Connect will automatically map exported columns to JDBC query arguments in
sequence from left to right.

Hope this helps.

Thanks,

Nivetha K

Sheethal · May 2020

Please check whether the following works:

jdbc.connect.url=jdbc:sqlserver://[servername]:1433/[databasename]

Anaplan - Export via jdbc

Answers

Categories