Ana-plan Connect to SQL Server

Hi Team,

Am trying to create a connection between Anaplan to SQL server to import the as list from MS Sql table to Anaplan. Please find the below script i am using.

@echo off

set AnaplanUser= roshin.rasheed@xxxx.com:password
set WorkspaceId= 123434343
set ModelID= sdsfsdsdsdsds

set Operation= -file "Source System from SampleLoad.txt" -jdbcurl "jdbc:sqlserver://localhost:1433;DatabaseName=Anaplan_Source" -jdbcuser ":" -jdbcquery "jdbc.query=select * from Anaplan_Tbl_1" -import"Source System from SampleLoad.txt" -execute

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 %Credentials% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
cmd /c %Command%
pause

*******************

Its not giving me output but giving error. Please advise where am wrong.

Regards

Roshin

Accepted answers

jesse_wilson

Hi Roshin,

In your properties file, you appear to be missing quotes around your select statement, which may be the cause of this, and just to verify you're using your workspace and model IDs, not names, in your script. If not, this will cause an error too. I'd also add -debug as the first option to your operation so it runs verbosely to give a bit more detail. There's no error, as such, the script is looping retrying a request somewhere, but not indicating where.

Thanks

All comments

jesse_wilson

Hi Roshin,

Could you share the error you're getting when you run this script?

At a glance, I see a couple things:

Your JDBC query should only be the select statement, you don't need jdbc.query=, just "SELECT * FROM Anaplan_Tbl_1"
There's no space between -import and the name: -import"Source System from SampleLoad.txt"

Try making those changes and let us know whether that resolves the error.

roshin

Hi Jesse,

I tried to do it a different way like below .

*****************************************

@echo off

set AnaplanUser= "roshin.rasheed@gds.ey.com"
set WorkspaceId= "asasasasasas"
set ModelID= "sasasasasasa"

set Operation= -file "Anaplan_Sql" -jdbcproperties "C:\Users\Roshin.Rasheed\Desktop\anaplan-connect-1.4\jdbc.properties" -import "Source Systems from Anaplan_Sql" -execute

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

*******************************

Properties file as below :

# JDBC Connection string (Oracle, Mysql, H2, etc.)
jdbc.connect.url=jdbc:sqlserver://localhost:1433;DatabaseName=Anaplan_Source

# JDBC login username
jdbc.username=

#JDBC login password
jdbc.password=

# (Optional) JDBC Fetch size, might not be applicable for all JDBC driver flavors.
jdbc.fetch.size=10

# Boolean flag, needs to be true if provided {jdbc.query} is a
# stored-procedure/function call.
# Defaults to false if no value provided, or invalid.
jdbc.isStoredProcedure=false

# SQL query could be Select or a Stored-Procedure/Function call. In case of latter,
# require {jdbc.isStoredProcedure} above set to "true".
# NOTE: SQL query needs to be parametrized with ? placeholders and values for
# placeholders needs to be provided below in {jdbc.params}.
jdbc.query=select * from Anaplan_Tbl_1

# JDBC parameters for parametrized SQL query in {jdbc.query} above.
# NOTE: Number of parameters must equal to the number of parameters provided in {jdbc.query} above.
#jdbc.params=1234,ABC

Now its showing me error as :

2019-03-14 14:20:16 [32mINFO [0;39m [35m15940[0;39m |-- Initializing Service...
2019-03-14 14:20:17 [32mINFO [0;39m [35m15940[0;39m |-- Authenticating via Basic...
2019-03-14 14:20:38 [32mINFO [0;39m [35m15940[0;39m |-- Retrying API request: Attempt (1)
2019-03-14 14:21:01 [32mINFO [0;39m [35m15940[0;39m |-- Retrying API request: Attempt (2)
2019-03-14 14:21:26 [32mINFO [0;39m [35m15940[0;39m |-- Retrying API request: Attempt (3)

jesse_wilson

Hi Roshin,

Thanks

roshin

Hi Team ,

The probelm was with our internal Network blocking the connection , We are good now.

Thanks again for your responses and help in this matter.

Regards

Roshin

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…