Export data from Anaplan to SQL server through Anaplan Connect script

Hi Anaplan Connect experts,

I need some help.

I know how to write a Anaplan script to load data from SQL Server to Anaplan module.

I need sample script from you, how to export data from Anaplan to SQL server.

Could you share me your sample script please?

 

Thank you & regards,

Anil

Best Answer

  • anildudam
    Answer ✓

    Hi ALL,

    I'm able to write script to export Anaplan data into SQL SERVER.

     

    Anaplan Connect script

    anildudam_0-1664673432151.png

    jdbc properties file

    anildudam_1-1664673695085.png

    Thank you

Answers

  • @anildudam 

     

    It's the same script with few changes, instead of PUT you need to use GET and an Export instead of Import. You also need to provide file location. See below

    Misbah_0-1664199915757.png

    Thanks,

    Misbah

    Miz Logix

  • Hi Misbah,

     

    Thank you for your response.

    Actually Im looking script for SQL Server.

    How to write SQL commands in the script to export data from Anaplan to SQL DB.

     

    Below script for JDBC properties file for SQL Server

    anildudam_0-1664235200180.png

     

    Import action script for SQL Server to Anaplan

    anildudam_1-1664235316740.png

     

    Need the similar scripts for to export data from Anaplan to SQL DB.

     

    Thank you,

    Anil

     

  • I've never tried it, or seen it actually, but in your JDBC properties file you would presumably change the query to an UPDATE statement.
    Let us know if it works...
    EDIT: it should be an INSERT
    INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)

    There's an example of this with V4.01
    Downloads - Anaplan Technical Documentation



  • Hi Mark,

    Thank you for your reply.

    I will try use of UPDATE statement to insert records in SQL DB.

    you mentioned INSERT statement, that will work for single records and hardcode values.

    I'm looking a sample script which some exports already tested to "update SQLDB table data with export action of Anaplan"

     

    I will update if any solutions works.

    Thank you.

     

  • Once you've got the csv file out of anpalan wouldn't any generic 'import csv into sql' solution work?
  • Hi George,

     

    Thanks for your reply, "any generic 'import csv into sql' solution" means other than Anaplan Connect scripts?

    We said to our client that, Anaplan Connect scripts works for both import and export to SQL. That's the reason looking for any solution with in Anaplan Connect.

     

    Looking for any leads from other experts.

  • fionafheren
    edited March 2023

    Hi @anildudam, I have a problem where i can't transfer from sql server to anaplan. My method is exactly the same as you made but still can't transfer the data. Do you have any solution?

  • Hi Fiona,

    Have you downloaded SQL JDBC jar file and placed in sub folder LIB? as similar like below screenshot.

    Share your Anaplan Connect batch script and jdbc properties file, so that I will check and guide you the same.

    Thanks,

    Anil

  • fionafheren
    edited March 2023

    Yes, I already download that SQL jdbc.jar and place it into the anaplan connect\lib folder. 

    here is my .bat:

    set AnaplanUser="email:pass"
    set WorkspaceId="xx"
    set ModelId="xx"
    set ServiceUrl="https://api.anaplan.com"
    set AuthUrl="https://auth.anaplan.com"
    set ProcessName="xx"
    set FileName="xx.csv"
    set jdbcproperties="D:\Anaplan\anaplan-connect-4.1.0\jdbc.properties"
    set Chunksize=1
    set DumpName="D:\Anaplan\anaplan-connect-4.1.0\Error"

    set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -chunksize %Chunksize% -file %FileName% -jdbcproperties %jdbcproperties% -process %ProcessName% -execute -output %DumpName%


    here is my properties:
    jdbc.connect.url=jdbc:sqlserver://localhost:1433;DatabaseName=xxxx;encrypt=true;trustServerCertificate=true;
    jdbc.username=xxxxxx
    jdbc.password=xxxxx
    jdbc.fetch.size=10
    jdbc.isStoredProcedure=false
    jdbc.query=SELECT col_1, col_2, col_3 FROM table_name

    Thank you for the help.

  • Hi Fiona,

    Scripts looks correct and I checked script result, but I don't see any error info and execution shows successful. I can't guide as I don't see any errors.

    Check is there any access right issue with SQL.

  • Hi @anildudam,

    I'll try to check regarding the access rights issue with SQL. I want to ask again regarding the import action in the .bat that I made. So I import blank data that has column headers to make it easier to map the data. Is that the right way? Because when I try to fill in the .csv file, the data that is transferred is the result that I uploaded using .csv not from SQL server.

    Thank you.

  • Hi Fiona,

    I'm not sure whether I share my email id here or not. Please send test email to anil.dudam@viseo.com. So that I will assist further.

    Thanks!

  • Hi @anildudam ,

    Thanks for the help, I already solved the problem.

  • Hi Fiona,

    That's great. May I know what was the issue?

    Thanks.

  • Hi @anildudam,

    I tried to create again the import action. Because I suspect the cause of not being able to import data from sql to Anaplan is in the import action.

    Thanks.

  • Hi Fiona,

    Got it.

    Thanks

  • Hi Anil,

    the files in your "Best Answer" aren't legible in the screenshots. Will you please post with the text so that is legible and, hopefully, able to be copied for our own attempts?

    Thanks!