Ana-plan Connect to SQL Server

roshin

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 

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

 

 

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:

1. Your JDBC query should only be the select statement, you don't need jdbc.query=, just "SELECT * FROM Anaplan_Tbl_1"
2. 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 ***

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

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

 

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)

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