Building Better Developers through Benefic Experiences

Menu

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 …

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 …

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 …

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 …

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 …

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 …

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 …

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. …

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 …

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 …