SSMSBoost Enhancements Help Boost Your Productivity in SSMS

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’ »

Automated Defragmentation of SQL Indexes – The ADoSI Script

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’ »

Pass or Fail SQL Agent Job Based on Batch Execution Results

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’ »

SSIS: Control Flow Using a FTP Server File Modification Date

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’ »

SSIS: Set FTP Connection and Credential Info From a Variable

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’ »

SSRS Reports – Displaying Superscripts and Subscripts

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’ »

SSIS ETL: Handling Character Encodings and Conversions

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’ »

A Polished Way to Run SSIS Child Packages With a Script Task

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’ »

Speed Up SQL Agent Jobs by Preventing Simultaneous Runs

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’ »

A Framework for SSIS ETL Development

The following is a brief generalized overview of a framework I developed on one of my projects for ETL processing using SQL Server Integration Services (SSIS). It is meant to provide an organized, consistent, centrally configured and managed, and disaster-recovery and audit friendly environment in which SSIS ETL processes can be developed and executed. This …

Continue reading ‘A Framework for SSIS ETL Development’ »