Search Results

Migrating SSIS Packages to SSIS Azure

Hello! In case you missed the announcement (and there were a lot of announcements during MSIgnite), SQL Server Integration Services is in Public Preview on Azure! I’ve written about it elsewhere in greater depth , but here are the headlines: It makes use of SSIS Scale Out , which was released as part of SQL Server 2017 . Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post. SSISDB is installed in either SQL Azure or on a Managed Instance. You

Migrating SSIS Packages to SSIS Azure part Two – Automating the Deployment

Hello! If you’ve read and followed through my previous post, you will have World Wide Importers Integration Services project running in SSIS Azure. It’s all very interesting, go and have a read . One thing that is missing form that guide, the documentation, and SSIS in general, is how to automate SSIS Deployments. In the WWI SSIS project, there are connection managers that we had to manually update the values of to get it to work post-deploy. This is exactly the opposite of what we want to do. Back when SQL Server 2012 was known as Denali, one of the

SSIS Package Execution In Azure Is Now Available

Well, it’s been some time coming but SSIS packages are the latest product to make the move from on premise to Azure. You can now take your SSIS projects and deploy them to the new Platform as a Service (PaaS) offering in Azure. The aim of the team at Microsoft was for users to take their current SSIS packages and just “lift and shift” these to Azure. So in development terms that means that there are minimum to no changes to be made in the solution at least. But before we get into the deployment and running of SSIS packages

Assist Deploy Is Available on GitHub

Hello! For some time now I have been working on automating SSIS deployments, and earlier this week I published my efforts on GitHub . But before I get into the what/how, let’s focus on the why and let me catch you up on how I got here… The task to take an ispac and deploy in and of itself is quite a straightforward process as there are multiple ways to do this . For those of you who want the abridged version of the linked post, the choices are as follows: Integration Services Deploy Wizard SSIS Catalog T-SQL API PowerShell

AssistDeploy 1.2 Is Now Live

Evening! AssistDeploy , our attempt to fully automate SSIS Deployments, is not yet a week old, yet we’re already on release 1.2 , which is our 4th release. If you’re wondering how we can be on a 4th release, when the number is .2, I suggest you have a read up on SemVer . Unlike the previous post , this will be brief. But like that post, I’m going to delve into why I’ve made the changes before what, so that the context is understood. What most IT projects attempt to achieve is take some knowledge of a subject matter

AssistDeploy Samples Repo Released

Hello! So AssistDeploy has been out for a little while now and so I decided to release the project I was using for testing during development. At the moment it uses just one scenario I used for testing as opposed to the several that I ran through. This is because initially I just want to provide a straightforward working sample. What became quite a big challenge was the fact that you need more than just an Integration Services project. So I've included the database projects which will allow the dtsx package to run successfully post-deploy. AssistDeploySamples The set-up process takes

PowerQuery – The power of M

I love PowerBI, actually I love PowerQuery. It's a great way to combine data from around your business, across the web from anywhere. What I really like is very little is done automatically, i.e. it doesn't do the nice data type detection you get with Excel that screws your imports if the data type in a column differs from the first few rows to the rest of the file. Does that make it difficult to use. No its not. The nice thing is that its very easy to add additional columns, change data types, use expressions, combine datasets, and do

Access Denied –How To Prevent a Failure Mid-Deploy

Hello! As part of any decent path-to-live, it is obviously crucial to deploy to other environments. This is crucial because not only do we need to test the changes being made, but just as importantly that the deployment will succeed. An ideally these environments should match as closely to production as possible. The less difference there is between production and all environments up to production, the greater the chances a deployment will succeed. Sounds simple, and perhaps even trivial, but if there is one thing that always, and I mean always catches deployments out, it’s permissions. Chances are production permissions

SQL Agent depLoyment Tasks Out Now!

Hello! In my time since leaving university, I’ve worked across the spectrum, from tester to DBA, it has always been abundantly clear that SQL Agent Jobs are one of those things that are really difficult to get into source control and deploy. Sure, you can script them out, but that doesn’t really factor in changes. And in many places, the phrase “whatever is in prod” has often been the answer to the question “what are the SQL Agent Jobs supposed to look like?” And frequently, relying on msdb being backed up as been the backup process for SQL Agent Jobs.

T SQL Tuesday: Shipping Database Changes with SSDT

Hello! Let’s see how this goes: this months subject for T SQL Tuesday is about shipping database changes, something we here are all familiar with. So I thought I’d make some notes about a tool I’m very familiar with, SQL Server Data Tools. The Good It’s free! SSDT works with Visual Studio Community up to Ultimate, and from Visual Studio 2015 onwards it comes with it’s own Visual Studio IDE. SSDT Has a NuGet package available. So you don’t need to install Visual Studio to get builds running, and crucially can control which version is used to compile at a

SSDTPokedex: Migrating a Database Into SSDT

Hello!   If you want to have the best chance of something being successful, you have to be committed to it from the start. That’s a pretty fatuous sounding statement, almost as bad as “to make something better you have to do more of the good stuff and less of the bad stuff”, so let me contextualise: if you desire to have good testing coverage on an application, then you need to be serious about testing form the first day you write code for the application. Be it manual or automated testing, you need to put the effort in terms