Anaplan Connect Alerts

PrevContributor

Hi,  I have set up several scripts for Connect, using JDBC to SQL etc., and all working ok, except there is a new requirement to alert someone if anything fails. They want to use a windows scheduler, but has anyone been able to write any scripts to send an email automatically when connect encountrers an error? Many Thanks

PrevContributor

Hi all, 
As I was unable to find anything in Connect to send alert emails, Ive managed to cobble together a PowerShell script and windows scheduler process to do this. Basic details are attached, and my example now runs successfully every day. 

I'm sure a proper programmer will eventually come up with something to solve this question, but in the meantime I'll use this method.
Enjoy,
Adrian

PrevContributor

Hi Adrian

First aplogies as this is not a response to your question but a request for help! I'm trying to connect to SQL Server 2012 using the JDBC Driver and Anaplan Connect but the operation seems to stop mid way reporting no errors and I have to terminate the process. I wonder if you can offer any tips on how you got your connection to work. Happy to share more details if you feel you can help.

Thanks
Sarina

PrevContributor

Hi Sarina, 
Have you added -debug at the start of your operation= ?

this will give you more details on any issues during the run.
By all means, send up your statements in this post.
Adrian

PrevContributor

Hi Adrian

Thanks for helping. Much appreciated. So, details:

• I'm connecting to Microsoft SQl Server 2012
  
• I've downloaded and copied the "sqljdbc42.jar" file into the lib folder
  
• I've tested uploading from a CSV to Anaplan and it all works
  
• I've added debug to my operation and it show that the process hangs on the last line below (no other errors reported):
  

Using C:\Projects\SalesTally\Tools\anaplan-connect\src\anaplan-connect-1-3-3-3.jar
Anaplan Connect 1.3.3-3
Java HotSpot(TM) 64-Bit Server VM (Oracle Corporation)/25.101-b13 (1.8.0_101)
Windows 7 (amd64)/6.1
Oct 18, 2016 11:18:24 AM com.anaplan.client.Service getTransportProvider
INFO: UA: com.anaplan.client.Service/1.3.3-3 (ApacheHttpProvider; GsonHandler; Java HotSpot(TM) 64-Bit Server VM (Oracle Corporation)/25.101-b13 (1.8.0_101); Windows 7 (amd64)/6.1)
Oct 18, 2016 11:18:24 AM com.anaplan.client.transport.ApacheHttpProvider get
INFO: GET /1/3/workspaces/ HTTP/1.1
Oct 18, 2016 11:18:24 AM com.anaplan.client.transport.ApacheHttpProvider$2 determineRoute
INFO: {tls}->http://10.16.3.231:80->https://api.anaplan.com:443 ()
Oct 18, 2016 11:18:25 AM com.anaplan.client.transport.ApacheHttpProvider checkResponse
INFO: HTTP/1.1 200 OK
Oct 18, 2016 11:18:25 AM com.anaplan.client.transport.ApacheHttpProvider get
INFO: GET /1/3/workspaces/8a81b09455d1444c0155efb51e372144/models HTTP/1.1
Oct 18, 2016 11:18:26 AM com.anaplan.client.transport.ApacheHttpProvider checkResponse
INFO: HTTP/1.1 200 OK
Oct 18, 2016 11:18:26 AM com.anaplan.client.transport.ApacheHttpProvider get
INFO: GET /1/3/workspaces/8a81b09455d1444c0155efb51e372144/models/C84FDDB7D8014E8FA9C707D633254D55/files HTTP/1.1
Oct 18, 2016 11:18:27 AM com.anaplan.client.transport.ApacheHttpProvider checkResponse
INFO: HTTP/1.1 200 OK
Oct 18, 2016 11:18:27 AM com.anaplan.client.transport.ApacheHttpProvider post
INFO: POST /1/3/workspaces/8a81b09455d1444c0155efb51e372144/models/C84FDDB7D8014E8FA9C707D633254D55/files/SS_JDBC_Test1 HTTP/1.1
Oct 18, 2016 11:18:28 AM com.anaplan.client.transport.ApacheHttpProvider checkResponse
INFO: HTTP/1.1 200 OK

Below is my Operation parameters in the batch file:

set Operation=-debug -v "xxx" -file "JDBC_Test1" -jdbcurl "jdbc:sqlserver://localhost" -jdbcuser "xxx:xxx" -jdbcfetchsize 10 -jdbcquery "SELECT TOP 20 * FROM AdventureWorksDW2012.dbo.DimEmployee" -output "C:\Projects\SalesTally\Tools\anaplan-connect\src\logs\SS_JDBC_Test1_Errors.log"

PrevContributor

Hi Adrian

I’ve resolved the issue. I had a look at what was listening on port 1433 by running: netstat –aon | more
 
I noticed that another programme (not SQL) was listening on port 1433. I stopped that programme and enabled the TCP/IP protocol in SQL Configuration Manager.
 
After I did that, I managed to upload data from SQL using a simple SELECT statement.

Thanks for your willingess to help. Out of curiousity, do you know if it's possible to call a stored procedure from the batch file instead of a SELECT statement?

Thanks
Sarina

PrevContributor

Thats good then.
So long as the JDBC allows "calling" to a procedure I dont see why not, although I havent tried it myself.
Let me know if it works
Thanks 

PrevContributor

Hi Adrian

FYI, managed to call an sp by adding this command in the -jdbcquery parameter: "EXEC sp_GetEmployeesByLastName 'Walters'"

Regards
Sarina

michel_maurel

Hi,thanks for these posts.

Have you tried to log into the Integration https://integration.anaplan.com/ for futher investigations ?
KR.
Michel.

PrevContributor

@Michael, I havent seen any in built alerting mechanism in these sites hence the question. Ive managed to work out a basic mechanism using PowerShell and a lot of "googling". I'll share the technique once I have documented it.

michel_maurel

Hi Adrian,
so many thanks for this great job and very valuable document.

By the way, as ar as directories are concerned I have posted a tip about the use of variable in anaplan connect batches to replace the constant rpetitio of the directory name with a variable.

You will find it under post "Anaplan Connect set Directory as a variable"
https://community.anaplan.com/forums/-/message_boards/message/4440376#_19_message_4440376

it's of poor value in comparision with your great job, but I find it usefull here and there.


my two cents.

Kind regards.
Michel.

 
 

PrevContributor

Thanks Michel and Adrian for the tips. All very useful and timely.

Best Regards
Sarina