Ana-plan Connect to SQL Server

Occasional Contributor

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 

Message 1 of 6
5 REPLIES 5
Regular Contributor

Re: Ana-plan Connect to SQL Server

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.



Jesse Wilson
Solution Architect - Data Integrations
Singapore
Message 2 of 6
Occasional Contributor

Re: Ana-plan Connect to SQL Server

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 INFO  15940 |-- Initializing Service...
2019-03-14 14:20:17 INFO  15940 |-- Authenticating via Basic...
2019-03-14 14:20:38 INFO  15940 |-- Retrying API request: Attempt (1)
2019-03-14 14:21:01 INFO  15940 |-- Retrying API request: Attempt (2)
2019-03-14 14:21:26 INFO  15940 |-- Retrying API request: Attempt (3)

Message 3 of 6
Regular Contributor

Re: Ana-plan Connect to SQL Server

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
Solution Architect - Data Integrations
Singapore
Message 4 of 6
Occasional Contributor

Re: Ana-plan Connect to SQL Server

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

Message 5 of 6
New Contributor

Re: Ana-plan Connect to SQL Server

Is the "Anaplan SQL" file in the Operation the sample file that was initially imported? If yes, should it not have a .csv extension? If not, what is the -file in the Operation expecting?

Should the Anaplan connect and the source Database be on the same server in order for this script to work?

 

Message 6 of 6