Export data from Anaplan to SQL server through Anaplan Connect script

anildudam

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

anildudam

Hi ALL,

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

 

Anaplan Connect script

jdbc properties file

Thank you

Misbah

@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

Thanks,

Misbah

Miz Logix

anildudam

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

 

Import action script for SQL Server to Anaplan

 

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

 

Thank you,

Anil

 

MarkWarren

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

anildudam

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.

 

GeorgeDuckett

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

anildudam

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

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?

anildudam

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

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.

anildudam

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.

fionafheren

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.

anildudam

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!

fionafheren

Hi @anildudam ,

Thanks for the help, I already solved the problem.

anildudam

Hi Fiona,

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

Thanks.

fionafheren

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.

anildudam

Hi Fiona,

Got it.

Thanks

jeffw1967

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!