How to Execute an SSIS Package on Azure from On-premise or IaaS Server

So you have SSIS packages deployed to Azure using ADFv2 and an Azure-SSIS Integration Runtime but you still have on-premise or IaaS SQL Server(s) and want to initiate the execution of your Azure hosted packages from your on-premise or IaaS server(s)...

As I like to say, don't fret! This is pretty easy to do using straightforward, tried & true on-premise SQL Server technologies:

1. Linked Server - A Linked Server is used to execute Azure SQL Database queries using four-part names (server.database.schema.object).
2. Stored Procedures - The SSISDB create_execution and start_execution stored procedures are used to initiate package execution.
3. SQL Agent Job - A SQL Agent Job is used to schedule the execution of the SSISDB create_execution, start_execution, etc. stored procedures.

There you have it. A rather painless way to execute Azure-based SSIS packages from your traditional SQL Server instance. This can be useful when migrating from on-premise and/or IaaS to PaaS.

Integration Services Catalogs Node Not Visible in SSMS When Connecting to Azure Server w/ Azure SSIS Integration Runtime

So you went through all the steps to configure and deploy SSIS on Azure using an Azure SQL Server, Azure SQL Database (for SSISDB), Azure Data Factory v2 (ADF v2), and the Azure-SSIS Integration Runtime type but when you connect to the SQL Server using SSMS you don't see the familiar Integration Services Catalogs node. You see something like this:

Don't fret. You likely configured and deployed everything correctly and the service is eagerly awaiting your requests. All you have to do is explicitly specify the SSISDB when connecting. To do this, click the Options>> button in the connection dialog and enter SSISDB as the database name.

Click Connect and you should be good to go!

SQL Azure: Building your first Microsoft database in the cloud

We’ve all been hearing about the cloud for a while and if you’re exposed to Microsoft at all that means Azure. If you’re a database person that means SQL Azure. So what’s it all about? 

Without getting too wordy, it’s more or less a high-availability, hosted version of Microsoft’s SQL Server database. Want most of the benefits of SQL Server but don’t have the hardware or human resources to run it yourself? SQL Azure might be right for you. Maybe you just want to do a quick proof-of-concept or prototype but don’t want to take the time of standing up a SQL Server. SQL Azure might be right for you.

I think I’m probably like a lot of developers in that new technologies can be a little hard to grasp and sometimes even a little scary until I get my hands on it and see it in action. The purpose of this post is to show you how easy it is to create and use database in the cloud on Microsoft’s Windows Azure platform.

Many developers probably don’t know that they get a free subscription to the Azure platform with any Visual Studio Professional, Premium, or Ultimate with MSDN license. Additionally, BizSpark program members also get the same free subscription that is available with Visual Studio Ultimate with MSDN. Details can be found here. If you don’t fall into the MSDN or BizSpark camps and want to give Azure a test drive but don’t want to cut into your budget there is also a free 90-day trial available at the time of this post (http://www.microsoft.com/windowsazure/free-trial/).

Time to start playing. At this point I’ll assume you’ve signed up, have a subscription and are logged in to the Windows Azure portal (http://windows.azure.com). You should see the Getting Started with Windows Azure screen below.

To begin the process of creating a database server and database, click on the Database link on the bottom left hand corner of the screen.

Now click on the Create a new SQL Database Server link.

Choose your subscription. My is named POC for proof-of-concept. 

Select the Region in which you’d like your server hosted.

Now set a password.

You’ll now have to add a firewall  rule to allow incoming traffic from the machine(s) in which you’ll be managing this server.

Once you’ve completed the process of creating a server you’ll come to a screen that provides all the details of your server. You’ll also see that you already have one database, the master  database. The master database is required and cannot be deleted. This database contains things such as permissions and usage metrics.

Now that we have a server we can create a database. Click the Create link in the ribbon-like menu at the top of the screen. Provide a name for the new database and choose an appropriate edition and maximum size. To be safe (in terms of billing) choose a 1 GB Web Edition database.

Congrats! You’ve now successfully created your first Microsoft database in the cloud.