Articles written by: Andrew Mosey

SQL Agent – Environment specific workflow

SQL Agent – Environment specific workflow

I like to keep my SQL Agent jobs in sync between environments, however not all jobs need to be run in all environments. To handle this, I’ve been using some TSQL to force a failure and end the job at a point. Here’s the overall job Within that, the TSQL for the ‘–Finish here if DEV–‘ step is To control the workflow, I’m setting the on success and on failure as follows:

read more

Controlling Chart axis in SSRS

Controlling Chart axis in SSRS

Had a tricky one today where the business wanted more axes to show on an SSRS report. Seems fair enough, cause as this example shows, a single line is pretty bland. After a bit of reading, I found a few clues over here. The concept is to convert a number to scientific notation, then use the first digit as a multiplier against 10 to the power of the exponent. This generates a nice round number, which is also divisible by 5. Here’s the code, I just pass the range of values on the chart (the calc is also used for the max of the chart), and the function returns an appropriate interval Here’s how my chart looks wtih the interval calcs: But… well that doesn’t look great either, because 600, 1200, 1800… and the maximum isn’t shown. So then the code ‘evolved’ again… End result:

read more

Subreport in a Sub Folder – working in Visual Studio and SSRS

Subreport in a Sub Folder – working in Visual Studio and SSRS

Subreports provide a great way to re-use SSRS content, however the implementation is a bit clumsy: SSRS 2016 doesn’t let you effectively hide reports (only folders) Visual Studio doesn’t let you have folders for SSRS projects (despite an 8 year old connect request with 290 up votes) Folders sort of work in Report Builder, but there’s no version control My design is for a user facing report with embedded parts that the user can’t see. This approach works in both Visual Studio and SSRS, and minimises maintenance. In SSDT, link to the full path of the Subreport This will work in SSRS once the Report is published, and the Subreport is published to ‘/Sales/ReportParts/’, however, we’ve broken the master report in Visual Studio Note the path referred to in the error – it’s ‘your project folder’ + ‘\bin\Debug’ + ‘your subreport path’ + ‘your subreport’. The challenge here is Visual Studio creates this content in \bin\Debug, and looks for it in \bin\Debug\Sales\ReportParts. We could create this path and copy content into it, but this would be a maintenance headache as we have to copy the subreport, data sources, and any shared data sets, and need to re-copy it when any of these change. Enter the Symbolic Link To get around this I’ve created a symbolic link to trick Visual Studio into thinking the content is at this path, when it’s really just a pointer to the live content in ‘\bin\Debug’. Here’s a guide on creating symbolic links in Windows, and here are the steps I ran in a command prompt (as adminsitrator) to create a link to ‘\Sales\ReportParts’:

read more

Hiding SSRS folders from users

Hiding SSRS folders from users

I’ve been looking into a way to allow users access to content without being able to see content in the SSRS 2016 Portal. This provides a way for developers to re-use shared objects such as Shared Data Sources and Shared Data Sets without confusing the user. The built in functionality to ‘Hide in Tile View’ is not suitable as: the default configuration for all users is to ‘Show hidden items’ there is no known way to overwrite this property for all users the property is overwritten each time you publish A better way to control visibility is via Security, however the built in ‘Browser’ role has too many rights for this need. Part 1 – Create a new SSRS Role Connect to Integration Services through SSMS Once in, expand Security, Roles, then Add Role Add a new role called ‘Reader’, and grant only rights to ‘View Reports’ and ‘View models’ Part 2 – Grant this role to users on folders you want to hide Connect to your SSRS Portal Edit Permissions for the folder that you want to hide Change the Users group to have ‘Reader’ rights rather than ‘Browser’ rights

read more

SQL Server code to pivot Key Value Pairs into fixed Colums

Square peg and a round hole.  Metaphor for a misfit or nonconformist.

Today’s requirement was to translate 550k rows of product data from a long list of name / value pairs into a wide table with up to 15 name value pairs per product.           I tried a few techniques to achieve this, and found SQL Server’s windowing functions to do a pretty good job of building a cross-tab from this flat list.

read more

Seeding an integer sort code into a Data Warehouse from a varchar

Seeding an integer sort code into a Data Warehouse from a varchar

I found this a bit of a challenge this morning, and as it’s my first use of windowing functions in SQL Server I thought I’d share. Our requirement was to add a sort code to our dimension tables for various levels. For some reason many of our business keys are varchars, however I really wanted to get sort codes as integers. Here’s the code: This generates three distinct sort keys, one for each level. Quite nifty I reckon.                       Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.

read more

Task Factory’s Upsert ‘Go Slow’ button

Task Factory’s Upsert ‘Go Slow’ button

If you’re using the Upsert component in Task Factory and notice upserts taking 10-20 minutes and would prefer them to take 10-20 seconds, try removing the tick from ‘Enable Identity Insert’.

read more

SSRS Missing Functionality

SSRS Missing Functionality

Just putting this out there cause surely I can’t be the only one missing this… If you install SSRS on SQL Server 2016 Standard Edition, quite a few of the menu items will be missing. Items such as: Mobile Reports KPI Reports Branding The solution.. install SSRS on Enterprise Edition.

read more

Script to split huge text file into chunks with headers

Script to split huge text file into chunks with headers

As a powershell newbie I’m pretty happy with this one, even if it relies on calling the split utility from cygwin and a dos copy command to perform… The split.exe is the windows port of the unix split command, I downloaded this as part of cygwin. However I didn’t want to install cygwin on the server so just copied the split.exe file across. This performs pretty well on our VM, it processed 200GB of text files into 64MB chunks in about 2 hours. It’s a one off process to seed the historical invoices into the DWH, so I have need to seek further enhancements.

read more

Capturing load errors with SSIS / SQL2016

Capturing load errors with SSIS / SQL2016

Using SQL 2016 and SSDT for Visual Studio 2015 I’ve been looking for a few weeks how to get an error in loading a .csv file to show the offending column, and the name of the error. There were some improvements to make this possible via a script object in SQL 2016,  however they’ve moved a few of the functions around which made the code posted online, including on the MS site, outdated. Here’s the C# code that is working with the latest releases:

read more