Swollen SSISDB caused by Continuous Deployment

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job. The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps SSIS Server Operation Records Maintenance...

January 10, 2017 · 1 min · matt40k

Another year, another SQL Saturday

Unfortunately this year I’ve not been able to make any of the pre-cons, however the (free) community day looks epic – the hardest part is going to be selecting which session to attend, the final one looking the worst. Aghhh!!! I’m going to have to roll a dice or something! CDC – Change Data Capture in Detail Uwe RickenWhat’s new in the SQL Server 2016 for a BI Professional Prathy KamasaniExploring the Azure SQL Database...

September 7, 2016 · 1 min · matt40k

Error building SSDT package

Today my colleague had a problem opening our BI solution, the solution had multiple projects, including 3 SSDT projects. Although the project builds correctly on both my machine, the build machine and another colleague machine it refused to build stating that the reference to the object in another project was invalid. After thinking for a few moments, I remembered I had seem this before. The problem was a bug in SSDT....

June 10, 2016 · 1 min · matt40k

Logging ConnectionStrings in SSIS

Another day, another reference to an old Jamie Thomson blog post– today it was getting the ConnectionString to output to the information. Admittedly I wasn’t looking for how to fire the connection string into an information event, just the ConnectionString, still another great idea and its useful for debugging.  

June 9, 2016 · 1 min · matt40k

MDSCHEMA_CUBES DMV not returning all cubes

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentationto feed the foreach loop....

June 7, 2016 · 2 min · matt40k

New laptop, error running SSIS package

So today I went to run a SSIS package on my new laptop and bam, error message. Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails. —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML....

June 1, 2016 · 1 min · matt40k

Automatic build and deployment process for Microsoft BI

I’ve previously blogged about how I was going to detail how I build and deploy my Microsoft BI projects and to be honest, I’ve been putting it off. The main reason is my work has been looking at Visual Studio Team Services, formally Visual Studio Onlineand I was looking at using the Build functionality that is included. This should make the whole setup process a lot easier for anyone else trying to replicate my setup....

February 1, 2016 · 4 min · matt40k

Automated SQL Server BI deployments with OctopusDeploy

Today, my pull request for the SSAS deployment script was merged into the Octopus DeployCommunity Library. So what is Octopus Deployand why should I care? Octopus is a friendly deployment automation tool for .NET developers. But don’t be fooled. Octopus Deployisn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopusto deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008....

January 15, 2016 · 3 min · matt40k

Joys of C#

On Friday, I had a problem raised, they want to change one of the automatic rename jobs which renames and moves export files to a shared drive. Lucky, I had created a SSIS package, which was a SQL Job that runs on a scheduled every 15 mins, which uses a script task to perform the logic. Because we also used OctopusDeploy, this really was going to be a 5 min change....

October 10, 2015 · 1 min · matt40k

Missing SSIS toolbox in Visual Studio 2013 / SQL Server 2014

Stupidly today, I accidently closed the SSIS toolbox. After a bit of unsuccessful searching in the Visual Studio menus, I gave up and done the IT norm and Google it – hoping I wouldn’t have to reinstall. Luckily I found James Serrahad already blogged about it, the only difference is Microsoft has changed the icon, its still in the same place at least So when is a Visual Studio Toolbox, not a Toolbox?...

September 2, 2015 · 1 min · matt40k

SSIS jumping in Visual Studio 2013 / SQL Server 2014

One really annoying bug in SSIS when using Visual Studio 2013 / SQL Server 2014 is the object jumping, so when you drag an object like a sequence container down the object shoots to the bottom. Thankfully the fix is simple. Close Visual Studio and apply SQL Server 2014 SP1to your machine.

September 1, 2015 · 1 min · matt40k

Documentation

One of the problems we have is documentation and how to keep it up-to-date. We tend to deliver a new batch of reports as part of a CR (Change Request) then make changes as part of BAU (Business as usual) unless its a particularly complex or time consuming change. The problem is often BAU changes happen rapidly often within a short time frame and time isn’t always allocated to updating documentation....

August 30, 2015 · 2 min · matt40k