免费的网络的爱,分和业力,我am sharing my struggles with Anaplan Connect setting up a JDBC connection with SQL Server. Purpose: The below has been documented to assist with setting up a JDBC connection to a SQL Server database, the version of JDBC driver used will be dependant on the database version you are connecting to. The instruction does not go into the detail of using certificates for authentication. This is not intended to be a how to use Anaplan connect but rather how-to setup the JDBC connection for SQL Server – not obvious for non-technical resources. Pre Conditions: Database: SQL Server 2016 Anaplan connect installed, managed, and scheduled on a Management Virtual Machine Service account setup in Active Directory which allows for access to the network share folders and RDP to virtual machine where Anaplan Connect is installed. Service account also has read only access to the SQL Server database through Windows Authentication (Integrated Security). Setup on virtual machine: Firewall: Whitelist Anaplan.com proxy Java: Install directory C:\Java\jre1.8.0_271 Microsoft JDBC Driver 8.4 for SQL Server: Install directory: C:\Program Files\Microsoft JDBC DRIVER 8.4 Install files: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 SQL Server Management Studio (optional) Install directory: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18 Install files: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 Purpose: create/test SQL queries (not mandatory for executing Anaplan Connect BAT files) Anaplan Connect: Install directory: C:\anaplan-connect Directory structure created in C:\ Anaplan-connect Certs Data Logs Scripts Anaplan libraries: Copied “mssql-jdbc-8.4.1.jre8.jar” from directory “C:\Program Files\Microsoft JDBC DRIVER 8.4” to directory “Anaplan-connect\lib\” Environmental variables – Create “New”: Variable name: “Path” Variable value: %PATH%; C:\Program Files\Microsoft JDBC DRIVER 8.4\auth\x64 HOT TIP: if you do setup the above environmental variable you will likely encounter the error “No suitable driver found…” Example Process: ExampleCompany.bat @Echooff rem For Windows OS : This example explains the usage of basic Authentication with Anaplan Connect. rem It also loads a source text file and runs an Anaplan import into a module. rem For details of how to configure this script visit https://help.anaplan.com/anapedia/Content/Downloads/DL_Downloads.html set AnaplanUser="
:" set WorkspaceId="" set ModelId="" set ServiceUrl="https://api.anaplan.com" set AuthUrl="https://auth.anaplan.com" set FileName="ExampleCompany.csv" rem set FilePath="C:\ExampleCompany.csv" -put %FilePath% set ImportName="ExampleCompany" set DumpName="C:\error\errorlog.txt" set Chunksize=1 set JDBCProperties="C:\scripts\ExampleCompany.properties" set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -chunksize %Chunksize% -file %FileName% -jdbcproperties %JDBCProperties% -import %ImportName% -execute -output %DumpName% 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=C:\anaplan-connect\AnaplanClient.bat %Credentials% %Operation% @Echo%Command% cmd /c %Command% pause ExampleCompany.properties # JDBC Connection string (Oracle, Mysql, H2, etc.) jdbc.connect.url=jdbc:sqlserver://;DatabaseName=;integratedSecurity=true # JDBC login username #jdbc.username=xxxxx #JDBC login password #jdbc.password=xxxxx # (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 where col1 = ? and col2 = ? #Note the below SQL Query does not connect to any tables/views or stored procedures but just test that the JDBC connection is working and the database privileges are setup correctly. Query can be reused on any database. jdbc.query=select '1' as code, ‘TESTJDBCCOMPANY' as name # 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
... View more
