Anaplan Connect Alerts

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

Best Answer

  • 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

Answers

  • 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
  • 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
  • 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"
  • 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
  • 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 
  • Hi Adrian

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

    Regards
    Sarina
  • Hi,thanks for these posts.

    Have you tried to log into the Integration https://integration.anaplan.com/ for futher investigations ?
    KR.
    Michel.
  • @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.
  • 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.

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

    Best Regards
    Sarina