Configuring Reporting Services Mail Delivery Using hMailServer SMTP To Gmail Relay

I have a SQL Server installation at home that I use as a sandbox and also for a small system I built for monitoring the stock market (more on that in another post to come). As part of that system I have a reporting services report that I’d like to email to myself on a set schedule during the day.

The SQL Server is on a Windows 10 machine which does not come with IIS and SMTP anymore, so I needed another solution to be able to get these reports emailed out. After Googling a bit, I found hMailServer, which is a free, open source email server for Windows. After you have downloaded and installed hMailServer, these are the steps to take to configure Reporting Services to email subscriptions through Gmail. This tutorial assumes that Reporting Services and hMailServer are on the same machine.

Configure Reporting Services

This is the easy part. Open Reporting services configuration manager, go to E-mail settings, and set them up as follows (replace example@gmail.com with the email address you are going to be using):

Configure hMailServer

This is the part I struggled with a little bit. I’m not really an SMTP/email guru. To setup the SMTP relay in hMailServer, go to Settings->Protocols->SMTP and select the Delivery of e-mail tab. Fill in your gmail settings here.

Remote host name: smtp.gmail.com

Remote TCP/IP port: 465

User name: example@gmail.com

Connection security: SSL/TLS

Now under Settings->Advanced->IP Ranges, click on My computer and make sure Require SSL/TLS for authentication is not checked, and make sure you do not require authentication for SMTP, as pictured below.

Gmail Settings

Gmail has a setting called “Allow access to less secure apps”. this needs to be enabled in order for this to work. If it is not enabled, you will probably get an email like this.

Debugging

If you are having trouble, these are some steps to take to get to the bottom of it.

  1. Enable logging in hMailServer. Go to Settings->Logging and enable logging for SMTP.
  2. Check the message on the Reporting Services subscription page.
  3. Check your spam folder – the reports might be going there.

 

 

SSIS Error: The component is missing, not registered, not upgradeable, or missing required interfaces.

I got a weird error the other day in SSIS that said “Error: The component is missing, not registered, not upgradeable, or missing required interfaces.” It was referring to an XML source component in a data flow. I am not sure if this error is specific to XML sources, or it just occurred because this happened to be the first component that got initialized. I am using Visual Studio 2015 Community and SQL Server 2016. After much wailing and gnashing of teeth I figured out how to fix the the problem. You need to change the “Target Server Version” property of the SSIS project (right click on the SSIS project and select properties to get there) to “SQL Server 2016”. Originally it was set to “SQL Server vNext”.

Moving An Email To A Folder In Gmail Using Python

I receive daily activity statements from my broker and I wanted to download them, extract the data into a MySQL database and then archive the statement on the hard drive. The first part is downloading the attachment, and then moving the email to another imap folder, which is something I had trouble with in Gmail. I use Python to do it, and the email and imaplib packages. Here’s a look at the code that moves all the messages in the inbox from a single recipient to another imap folder.

 

import Messaging, Util, Const
import email, imaplib, os

m = imaplib.IMAP4_SSL(Const.IMAP_SERVER)
m.login(Const.IMAP_USER, Const.IMAP_PWD)
m.select()

resp, items = m.search(None, 'FROM', '"donotreply@interactivebrokers.com"')
items = items[0].split()

for emailid in items:
    resp, data = m.fetch(emailid, "(RFC822)")
    if resp == 'OK':
        email_body = data[0][1].decode('utf-8')
        mail = email.message_from_string(email_body)

        if mail.get_content_maintype() != 'multipart':
            continue

        if mail["Subject"].find("Activity Statement") > 0:
            print("Moving message " + mail["Subject"] + " to " + Const.IMAP_DEST_FOLDER)
            result = m.store(emailid, '+X-GM-LABELS', Const.IMAP_DEST_FOLDER)
            #result = m.store(emailid, '+FLAGS', '\\Deleted')
            mov, data = m.uid('STORE', emailid , '+FLAGS', '(\Deleted)')
            m.expunge()

m.close()

I use another file called Const.Py to store all my configurations, so change those to whatever you need. The key here is to change the label with the line “result = m.store(emailid, ‘+X-GM-LABELS’, Const.IMAP_DEST_FOLDER)”, and then add the “Deleted” flag, before expunging the inbox.

 

Market Analysis – Nasdaq Futures

The Nasdaq futures (NQ) broke out of resistance at 4,441.50 but got promptly rejected at 4,450. That failed breakout look very bearish to me, and I think we could find ourselves going lower in the next few days.

NQ - 360 Minutes - 8 July 2015
NQ – 360 Minutes – 8 July 2015

Below us there is good looking demand at 4,205.75, which is where I’ll be starting to look for a long.

Looking at the weekly chart, with indicators courtesy of Emini-watch, you can see that we put in a pullback to end of trend, with a yellow amateur bar trying to get long at the top. The picture is quite bearish.

NQ - Weekly - 8 July 2015
NQ – Weekly – 8 July 2015

 

I currently have a short order placed at 4,401.75, from a small 2 minute zone.

Adding an OCO (Stop and Target) To An Existing Position In Interactive Brokers Trader Workstation

Most of my trading has been done on Tradestation, which still in my opinion has one of the best platforms out there as far as ease of use goes. When I switched to Interactive Brokers I found it quite an adjustment getting used to Trader Workstation – the IB platform. You can do most everything TS does in this platform, but it is all done a little differently so it takes some time to adjust.

One of the things I battled with was adding an OCO for a target and a stop for an existing position. What I usually do is get into a position with a limit order and a stop, and then add the target at a later stage. It’s not a good habit, and something I am changing. Having said that, if you find yourself in a position, wanting to add an OCO (order-cancels-order) for a profit target and a stop, here is how you do it.

  1. In the classic TWS, click on the contract/stock/forex pair that you want to place the order for. For this example, lets say that I am long one contract of ES at 2070.
  2. Press the order button in the top left hand corner.
    TWS Order Button
  3. Lets place the stop first. On the basic tab, we’ll make it at 2068, GTC and a stop order.
    Order Ticket Stop Basic
  4. This is the important part. Go to the Misc. tab, and give the order a group name. It must be unique per trade, so you can’t use the same group name all the time. This is the part that links the orders into groups. I’ve called mine “ES_OCO_LONG_01_01”. Press Transmit.Order Ticket Stop Misc
  5. Now click the Order button again and this time we’re going to do the target. We’ll set our target as a LMT sell order, GTC with a price of 2080.Order Ticket Target
  6. Now go the the Misc tab, and paste in (or type) the group name exactly as you did for the stop order. Mine is “ES_OCO_LONG_01_01”Order Ticket Target Misc
  7. Press Transmit and double check you’re order in your Activity Monitor and you’re all done.

It’s a lot more work than doing it in TS, but the result is the same and once you get used to it, it really isn’t all that bad. The only thing I wish you could do was place multiple target orders at the same time.

Interactive Brokers