Bitesize SSIS: Deploying the SSIS Catalog

I hate SSIS. It seems to me that it is full of certain nuances and unless you are regularly developing SSIS packages, they are easy to forget or it is easy to miss specific important steps. I first started using SSIS back in 2005 when it was directly introduced to replace DTS, but even today I am constantly going around in circles whenever I have to return to write certain functionality.Therefore I have decided to put together a “Bitesize” series of posts that encapsulate simple operations in order to help not just you, but more importantly, remind me! Hopefully, this will save me time in the long run…

The SSIS Catalog (SSISDB) was first introduced in SQL Server 2012 and enabled faster and easier SSIS package deployment (particularly in SQL Server failover clusters) since (by its use) all packages and metadata would now live inside its database, and any other remaining metadata within the instance. This is, of course, a huge improvement over traditional SSIS Package store deployment (and covered in future posts), but for now, we will focus solely on simple standalone deployment.

Create the SSIS Catalog on your SQL Instance
Creating the SSIS Catalog for your instance is fairly straightforward, simply navigate to (and right-click) the Integration Services Catalog node in your instance via SSMS and select Create Catalog…

The Create Catalog wizard is a single page and all you really need to provide is a password which will be used to protect the encryption key which secures your SSIS Catalog secrets.

Note also in the wizard above, the panel message stating “You can manage the encryption key by creating a backup. If you migrate or move the Integration Services catalog to another SQL Server instance, you can restore the key to regain access to encrypted content“. The relevance of this operation will become obvious in later posts when we discuss deployment of the SSIS Catalog to an Availability Group.

You might also notice the option Enable automatic execution of Integration Services stored procedure at SQL Server startup. All this does is to automatically execute (or not) the SSIS cleanup job whenever the instance is started. You can view the automatic startup state of stored procedures by running the following code segment:

SELECT name,is_auto_executed 
FROM sys.procedures

If you want to change the automatic startup state of stored procedures then you can use the sp_procoption stored procedure.

Once your SSIS Catalog is created you should see the Catalog within the Integration Services Catalog node, a database called SSISDB under the Databases node, and a job called SSIS Server Maintenance Job under the SQL Server Agent Jobs node.

As you have seen, configuring your SQL Server for the SSIS Catalog is incredibly easy and regardless of your current SSIS package deployment strategy is something that you should use going forward (and also consider migrating your existing packages). In future posts, we will also cover SSIS Catalog deployment in a highly available configuration via an Availability Group (I will add the links here once they are published).

Want more Bitesize SSIS tips? Then keep an eye open for the other posts in the series!

Posted in SQLServerPedia Syndication, SSIS | Tagged | 1 Comment

Back in the Big Apple!

I am delighted to announce that I’ll once again be speaking at SQLSaturday New York on the 20th May and this is my Third time speaking at this event and my FOURTH time speaking at the Microsoft offices in New York, after last year speaking at SharePoint Saturday New York (since the SQLSaturday didn’t run that year).

I’m a huge fan of the event and obviously, it is a mammoth task for the organizers to put on given the location and size of the audience, but I have always had great fun and (unexpectedly) refreshingly good feedback speaking there.

Last year I took the Wife to New York, which was a really strange change, being my first time traveling with her (without kids) for over 12 years! And the year before was pure insanity (but great fun) spent in the company of the Hornernator, SQLWareWolf, my long time pal David Dye and a few other crazies! What happens in New York…. stays in New York!

This year I will be talking about In-Memory OLTP, and judging by the agenda, you will be spoilt for choice on this subject matter, but I hope to provide you with some useful materials (for attendees only!) and offer something different, so I hope you can join me there.

I will, of course, be soaking in the sights of the Big Apple once more, and hopefully visiting the Amazin’ Mets like I did last year! So if you are around New York City on the 20th of May, make sure you register for this brilliant event and come and say hello.

Posted in Public Speaking, SQLSaturday, SQLServerPedia Syndication | Leave a comment

Setting up Samba on Linux Mint (the easy way)

The Server Message Block (SMB) Protocol is a network file sharing protocol introduced by Microsoft and can be incredibly useful when moving files across multiplatform machines (particularly if your primary machine is a Windows desktop). Samba is a file and print sharing suite of utilities in Linux which uses and provides integration with other machines using the SMB transport.

What this quick guide covers

If you only want to provide basic folder sharing capabilities from your Linux distribution of choice, configuration and setup of Samba is (in my humble opinion) over complicated at best and a little bit messy at worst.

This quick guide is specifically targetted to the Linux Mint distribution (although will be applicable to many others) and only describes how to share your Linux filesystem folders and does not go into any detail regarding the advanced Samba functionality.

Even though Linux Mint attempts to make folder sharing more user-friendly, I have never had any success using the GUI based procedure, and have even struggled with the following method described in this article. Furthermore, I prefer to understand what is being configured behind the scenes, so I shall keep to the point and keep it simple.

The following procedure was tested on the latest release of Linux Mint at the time of writing (18.1 “Serena”) but I have also used this successfully against 17.1 “Rebecca”.

Configure the share

The first thing you need to do is configure your share in the samba configuration file.
Edit /etc/samba/smb.conf and scroll to the Share Definitions section inserting the following section (replacing the relevant names as required).

path = /media/mylinuxuser/Dropbox
valid users = mylinuxuser
read only = No
#create mask = 0777
#directory mask = 0777

The name in square braces is your desired share name, the path is obviously the real path to the folder you are sharing and the create mask and directory mask parameters define what permissions are assigned to files and directories created through the share. In the section above, the masks are commented out and Samba defaults should be sufficient, but you can override and provide less restrictive permissions if necessary (from a security perspective, please understand what you are doing first!). Ensure you provide at least one valid user to access the share.

To check share setup run:


Install Samba
If Samba is not installed (you can check this by sudo service –status-all|grep smbd OR sudo service –status-all|grep samba)

sudo apt-get install samba

Add SMB password
Ensure you add an SMB password for every valid user that you wish to access the share:

smbpasswd -a mylinuxuser

Restart SMBD daemon
Finally, for the new share to be visible to your remote device you will need to restart samba (you will also need to do this every time you add a new share or reconfigure an existing one):

sudo service smbd restart

That is all there is to it. Once you have followed these steps, your share will be available to your remote SMB client from your Linux Mint desktop.

Posted in Linux, Networking, Windows | Tagged | Leave a comment