Anaplan - Export via jdbc

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

 

Answers

  • Hi,

     

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

  • what is the solution to over come this issue
  • Are there any rows in the view you are exporting? There is a long-standing bug that is triggered when there are no rows.

  • yes we have around 5 to 6 rows are there in exporting file
  • 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?

  • Hi,

    also check if you have duplicated values:

    ABerenguela_0-1589203123814.png

     

    try the insert with IGNORE

    ABerenguela_1-1589203186180.png

     

    Regards

     

     

  • 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
  • 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

  • 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
  • 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]

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

  • 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

     

  • Please check whether the following works:

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