You have a multidimensional SQL Server Analysis Services (SSAS) cube that your users connect to with Power View for ad-hoc reporting. You’d like your users to be able to use the mapping functionality of Power View to map specific locations of interest. You expose your locations as latitude and longitude attributes in your Locations dimension, …
Continue reading ‘Power View – Use Latitude and Longitude Stored in SSAS’ »
SQL Server Management Studio, or SSMS, is the standard and widely used tool for the development and management of Microsoft SQL Server components. But did you know that there are many 3rd party add-ins available for SSMS to provide features and enhancements to the tool? SSMSBoost is one such add-in, and provides several useful additions …
Continue reading ‘SSMSBoost Enhancements Help Boost Your Productivity in SSMS’ »
The Automated Defragmentation of SQL Indexes, or ADoSI, script is an excellent option for maintaining your Microsoft SQL Server database indexes. ADoSI is made to be a “drop in and go” index defragmentation solution, simple to set up and get your database indexes on their way to being more quick and efficient in minutes. It …
Continue reading ‘Automated Defragmentation of SQL Indexes – The ADoSI Script’ »
Here’s a simple example of how you can set up a SQL agent job step to pass or fail based on a batch file’s execution results. The Batch File Let’s set up a sample batch file, save it in “c:\test\test_fail_batch_.bat”, and add the following code to it: First, a simple move operation is performed on …
Continue reading ‘Pass or Fail SQL Agent Job Based on Batch Execution Results’ »
The Scenario, or Where Did My FTP File’s Modification Date Go? You have this ETL process where, like it or not, you have to pull a source file from a FTP server. You’ve decided you want to add a little intelligence to the process. Maybe you only want to reprocess the file if it’s changed …
Continue reading ‘SSIS: Control Flow Using a FTP Server File Modification Date’ »
The Problem With Setting Up SSIS FTP Connections Your ETL environment is set up to follow a proper development lifecycle. Your SSIS ETL packages use package configurations and connect to systems with different connection strings depending on whether you’re in your development, staging, or production environments. You try to set up your FTP connections to …
Continue reading ‘SSIS: Set FTP Connection and Credential Info From a Variable’ »
At some point in time you may find that you need to display numbers in superscript or subscript format in a SSRS report. I ran into it recently in a report that required footnotes describing the contents of columns. If you ever need to display chemical or mathematical formulas you may also find number subscripting …
Continue reading ‘SSRS Reports – Displaying Superscripts and Subscripts’ »
The Character Encoding Issue You’ve been happily loading data into your data warehouse for years. But suddenly your SSIS ETL processes have started to fail. You dig into the issue and find that there are some funny looking text characters now arriving in your data. So now what? I often see this this scenario occur …
Continue reading ‘SSIS ETL: Handling Character Encodings and Conversions’ »
The SSIS Execute Package task works well for running child packages from a master package, and in many cases using it will fit your needs. But what if you are looking for something more powerful? Maybe you want to dynamically populate variables in the child package without dealing with master/child package configurations and their restrictions. …
Continue reading ‘A Polished Way to Run SSIS Child Packages With a Script Task’ »
I’ve seen the time it takes a SQL agent job to complete on a resource-limited system increase tenfold when running alongside another job compared to when it’s run individually. I’ve also seen several jobs run simultaneously without any hiccups. Parallel Processing is a good thing, and your goal when scheduling SQL agent jobs should be …
Continue reading ‘Speed Up SQL Agent Jobs by Preventing Simultaneous Runs’ »