Creating an Alexa Skill in C# – Step 2

If you haven’t done so already, check out Step 1 to define your Alexa Skill.

After defining the Alexa Skill in Step 1, you are ready to set up Visual Studio in Step 2.

AWS Account and AWS Toolkit Extension

Before you can set up an Alexa project, you need to create an AWS Lambda project.  Lambda functions are just class libraries that are hosted in the AWS Lambda cloud service.   To create one, you need two things:  the Amazon AWS Toolkit Extension and an AWS Developer Account.  You can install the Extension in VS 2017.

snip_20180511104603

You can create the AWS Developer Account at https://aws.amazon.com/.

With the AWS NuGet package installed and the Developer Account set up, you are ready to set up the AWS Security.  You do that here:  https://console.aws.amazon.com/iam.

Securing Your AWS Account and Lambda Function

In IAM Management Console, click on Groups on the left.  Create a group for your Developers that has Admin Access.  Create a group for your Apps that has AWSLambdaFullAccess

Then, click on Users on the left and create two accounts.  One for yourself, of type AWS Management Console access, assigned to the Developers group.  And, one for your app, of type Programmatic access, assigned to the Apps group:

snip_20180508155233

snip_20180508155436

Next, create an AWS Lambda Role by selecting Roles on the left side.  Then make the following selections:

snip_20180508155822

snip_20180508160005

snip_20180508160125

With the security Groups, Users and Role configured, you are ready to create the Solution for your Alexa Skill.  The easiest way to do this is to use one of the sample projects in the Alexa Git repository.

Getting the Alexa Skill Sample Solution

Navigate to the Alexa GitHub repository in your browser to see what’s available.

I used the alexa/skill-sample-csharp-fact sample as my starting point.  It has a ton of code in it that is ready to go, I just had to add something specific to my skill and I was off to the races.  You can get the sample from the command line (Start | Run | Cmd).  Make the directory/folder you want to host the project in with the md command:

C:>  md \_GitHub
C:>  md \_GitHub\Alexa
C:>  cd \_GitHub\Alexa

And then type:

git clone https://github.com/alexa/skill-sample-csharp-fact.git

You will find the C# Solution file here:

C:\_GitHub\Alexa\skill-sample-csharp-fact\lambda\custom\sampleFactCsharp.sln

Copy the sample solution to your own folder:

C:\_GitHub\Alexa>  md MyAlexaSkill
C:\_GitHub\Alexa>  cd skill-sample-csharp-fact\lambda\custom
C:\_GitHub\Alexa\skill-sample-csharp-fact\lambda\custom>  
    xcopy *.* \_GitHub\Alexa\MyAlexaSkill\*.* /S

Open the solution in VS 2017.  (Be sure to update to the latest version of VS — 15.7 as of this writing — as it has some cool new features!)

In Step 3, we will look at the sample solution and start to modify it to work with the skill we defined in Step 1.

 

Creating an Alexa Skill in C# – Step 1

Recently, I decided to make an Alexa skill that I could play on my boss’s Alexa.  At first, I was doing it as a gag, but I figured that wouldn’t work as he has to actively install my skill onto his Alexa.  Now it reads some stats from one of our Azure databases and publishes those in a conversation.  Here’s how I built it.

Step 1:  Creating the Alexa Skill Model

I don’t know any of the other languages that you can write a skill in, so I chose to write it in C#.  That means getting a bunch of bits and loading them into Visual Studio.  But first, you’ll need to start the process of creating a skill.  At the time of this writing, these are the steps I took.

  1. Start Here.
  2. Click the Start a Skill button.capture20180507114817423
  3. If you don’t have an Amazon Developer account, create one.
  4. Eventually, you’ll get to the Alexa Skills Developers Console where you can click the Create Skill button.  capture20180507115228773
  5. Give your skill a name.  I’m calling mine:  Simon’s Example Skill.
  6. On the Choose a Model screen, select Custom. capture20180507115624173
  7. Then click Create Skill.
  8. You should now be on the Build tab for your Skill.  Notice the tree view on the left and the checklist on the right.capture20180507115839651

Invocation Name

The Invocation Name is the phrase that an Alexa user will use to launch/run/start your Alexa Skill.  It could be “joe’s hot dog recipes” or some such.  It needs to be lower case, there are some restrictions on the characters you can use, and any abbreviations you use need periods to separate the letters so Alexa knows to read the letters and not pronounce the word.  Read the Invocation Name Requirements for more details.

Click the Invocation Name link in the tree view on the left or the first option in the checklist on the right.  Then give your skill an Invocation Name.  I named mine:  “simon’s example”.

Intents

The Intents are the various functions your skill can perform.  For example, stating today’s date, looking up some data, figuring something out, etc.  Let’s do all three.

First, my skill is going to provide today’s date, so I’m going to name my first Intent, “GetTodaysDateIntent”.  My skill is also going to look up some data in an Azure SQL Database, so I’m going to name my second Intent, “DataLookupIntent”.  Lastly, I want to figure something out, like the average temperature in a major US city.

Utterances

The Utterances are the phrases an Alexa user might say to trigger your Intent (function).  You should put in several Utterances using synonyms and different phrasing so Alexa has a better chance of triggering your Intent instead of responding with “I don’t know how to do that.”

For the GetTodaysDateIntent, I added the following utterances:

snip_20180511131124

Slots

Within an Utterance, you can have Slots (or placeholders), that represent the multiple options for that slot.  For example, if my table has the count of animals per household per neighborhood per county in it, I might want to create slots for Animal Type, Household Size, Neighborhood Name, and County Name.  You can do this by typing a left brace { in the Utterance box.

capture20180507123258765

Here are three of my sample utterances for the DataLookupIntent:

capture20180507123605384

Once you have created a slot, you need to populate it with options.  You do this in the bottom half of the Utterance screen.

capture20180507123836958

You can easily select one of the pre-defined Slot Types in the drop-down.  In my case, Amazon has a list of Animals, so I’ll pick AMAZON.Animal in the first slot.

I need to manually add a few Counties for the second slot though.  And at this time, you don’t want to click Edit Dialog (though it’s tempting).  Instead, you want to define your own Slot Type by clicking the Add (Plus) button next to Slot Type in the tree view on the left:

capture20180507124646880

For example, here is the custom Slot Type for County Name:

capture20180507130009338

Notice the synonyms column.  This is important if there are synonyms, for example, a 1 person household and a single person household are synonymous.  So be certain to add any synonyms you can think of.  Here is my custom Slot Type for Household Size, notice the synonyms off to the right:

capture20180507130107411

Now that you’ve defined some custom Slot Types, you can click on the Slot names under the Intent in the tree view on the left and select the newly created Slot Type for each Slot.

capture20180507130205126

For the GetAverageTemperatureIntent, I added one Utterance:

snip_20180511131952

And configured the {City} slot as follows:

snip_20180511133037

Finally, you can Save your Model and Build it by clicking the buttons at the top of the screen:

capture20180507130345077

Hopefully, the model will build and we are ready to move on to Step 2.  If the model doesn’t build, check the bottom right of the screen for a list of the errors:

snip_20180511132317

Fix the errors until the model builds:

snip_20180511133106

Then go to Step 2.

 

 

 

 

Connecting to IBM DB2 zOS from Azure Data Factory v2

Connecting to IBM DB2 zOS from Azure Data Factory v1 was a matter of setting up the Azure Data Gateway on an on-prem server that had the IBM DB2 Client installed; creating an ODBC connection to DB2 (I called it DB2Test).  Then, in the Data Factory v1 Copy Wizard, Select the ODBC source, pick the Gateway, and enter the phrase:  DSN=DB2Test into the Connection String.  This worked for us.

Azure Data Factory v2

First, the Azure Data Gateway is now called “Hosted Integration Runtime”.  So download and install the IR client on your on-prem gateway machine.  On my machine, it auto-configured to use the existing Data Factory Gateway configuration, which is NOT what I wanted.  After uninstalling and reinstalling the IR client a couple of times, it stopped auto-configuring and asked me for a key.  To get the key, I had our Azure Dev configuration guy run the following PowerShell:

Import-Module AzureRM
$dataFactoryName = "myDataFactoryv2NoSSIS"
$resourceGroupName = "myResourceGroup"
$selfHostedIntegrationRuntimeName = "mySelfHostedIntegrationRuntime"
Login-AzureRmAccount
Set-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $resouceGroupName -DataFactoryName $dataFactoryName -Name $selfHostedIntegrationRuntimeName -Type SelfHosted -Description "selfhosted IR description"
Get-AzureRmDataFactoryV2IntegrationRuntimeKey -ResourceGroupName $resourceGroupName -DataFactoryName $dataFactoryName -Name $selfHostedIntegrationRuntime

I then pasted the Key into the Integration Runtime Configuration screen, and it connected properly to myDataFactoryv2NoSSIS.  Tada:

snip_20180124073749

Next, is to test the connection to DB2.  I went to the Diagnostics tab, entered the DSN and credentials, just like I did for Data Factory V1:

Failed to connect to the database. Error message: ERROR [HY010] [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

Dang! Much googling later, I found this obscure note.

I added the phrase “Autocommit=Off” to the DSN in the connection string, and voila, the connection worked.  So my final diagnostic looked like this:

snip_20180124074603

YAY!!

 

SQL Saturday Charlotte 2017

I am excited to announce I am finally ready to present my advanced ETL Framework with Biml seminar at SQL Saturday Charlotte on Oct 14.  And I have a 10AM slot!!!   Wooo!

SQLSat683_Speaking_300x225

Implementing a SSIS Framework and enforcing SSIS Patterns (with Biml).

(Using Biml to Automate the Implementation of a SSIS Framework)

Let’s use Biml to automate the implementation of a standard SSIS Framework. I.e. Logging, error handling, etc. Business Intelligence Markup Language (Biml) is great at automating the creation of SSIS packages. With Biml we can generate a template package that implements a standard SSIS framework. In this fast-paced session, we will create the tables and load some metadata, write the Biml to implement logging and error handling, and generate some packages that implement our standard framework. In this session, you don’t need to know Biml, but some familiarity with XML, TSQL or C# will help. By the end of this session, you will know how to use Biml to automatically generate packages that implement a simple SSIS Framework with Audit Logging and Error Handling.

BMI KeyNotes – Toastmasters Speech on Biml

As practice for delivering a 50-minute seminar to my fellow ETL Developers at BMI, I presented Speech 5 from the Toastmasters Technical Presentations manual.  It was a whopping 12-15 minute speech and I didn’t go over time!!!   Yay!!!  (I normally blow through the red card and end up a minute or two over my time before the Toastmaster yanks off the podium.)

I think the speech went well.  I only got off script a couple of times.  (But at least I didn’t read it verbatim, which I too often do.)  I lost some people during the demonstration portion of the speech, but that is where I cut a 40-minute demonstration down to 4 minutes, so I knew it was going to be too fast and too technical.  I do hope that most of my non-technical audience at least got a glimpse into the world of an ETL developer, even if they didn’t understand it fully.

Please use the comments section below to ask questions and provide feedback.  I welcome comments, questions, and constructive criticism about the speech and the content.  Thank you.

Below are links to the PowerPoint slide decks I used, the speech I delivered and the support files for the demonstration I gave.

How to move a ton of data from the Mainframe to the Cloud – PowerPoint

How to move a ton of data from the Mainframe to the Cloud – Speech

Using Biml to Automate the Generation of SSIS Packages – PowerPoint

Intro to Biml Speech – Support Files

SQL Saturday Atlanta!

I am excited to announce that I will be giving my BIML seminar in Atlanta in July!!! Here’s the abstract for my session:

SQL Saturday 652

How to move a ton of data from the Mainframe to the Cloud (with Biml).

So, you need to move data from 75 tables on the mainframe to new tables in SQL Azure. Do you: a) hand code one package to load all 75 tables, b) hand code 75 packages that move a table each, or c) wish there was a better way?
There is! Business Intelligence Markup Language (Biml) can automate the creation of packages so that they all follow the same script. In this session, we will create some simple metadata to be able to generate multiple packages and their associated connection managers. You will see Biml in action. You will see the XML that Biml uses to define packages and connections. You will see the C# code that Biml uses to fetch metadata and dynamically generate packages in SSIS. And you will see packages and connection managers generated from Biml before your very eyes.

SQL Saturday!!!

Yay!!! I’ll be speaking about Biml at SQL Saturday in Chattanooga in June!
I’m very excited. In typical fashion, I will probably cram too much into the hour, but I’ll try not to. Here’s the abstract for my session.

SQL Saturday

Using Biml to Automate the Generation of SSIS Packages

So, you need to move data from 75 tables on the mainframe to new tables in SQL Azure. Do you: a) hand code one package to load all 75 tables, b) hand code 75 packages that move a table each, or c) wish there was a better way?
There is! Business Intelligence Markup Language (Biml) can automate the creation of packages so that they all follow the same script. In this session, we will create some simple metadata to be able to generate multiple packages and their associated connection managers. You will see Biml in action. You will see the XML that Biml uses to define packages and connections. You will see the C# code that Biml uses to fetch metadata and dynamically generate packages in SSIS. And you will see packages and connection managers generated from Biml before your very eyes.