Category Archives: Business Intelligence

Automating Web App Deployments with SSIS

This is just a quick note to let everyone know I’ve uploaded the project files from my Pragmatic Works webinar on Automating Web Application Deployments with SSIS. You can find the link on my Presentations page. If you missed the presentation you can view the recording of it here.

The zip file includes a readme document that highlights some of the important things to setup for project as well as an explanation of the different solution files inside the zip file. If you have any questions or problems feel free to leave them in the comments below!


Pragmatic Tools: DTSxChange (Or DTS Must Die!)

Ah DTS packages…they suck. Seriously, it’s 2011. It’s been over a decade and we still have SQL 2000 and DTS packages lingering around our shops…sitting there…taunting us…asking to be upgraded but HOW? There’s SO much work having to learn SSIS, figure out what the current DTS package even does, figure out what shiny new components in SSIS are equivalent to that legacy crap we created a decade ago and recreate it on the new platform! Well fret not, we at Pragmatic Works got you covered with a nifty tool called DTSxChange that will actually convert your DTS packages to SSIS for you!

DTSxChange is a powerful tool that converts your legacy DTS packages to SSIS (can convert to SQL Server 2005 or 2008 SSIS) for you with minimal effort. In addition to saving you a ton of work, the tool actually tells you how much time (and money) you’re saving using this tool over a manual effort via the DTSxChange Profiler feature. This tool allows you to scan all of your current packages and it generates a really nice report (read also: bosses/managers/CFOs love this)

DTS sucks
Ooh shiny!

So now that you know the scope of the work you’re looking at, let’s get to the nitty gritty and start converting! The conversion portion of the software uses an easy step-by-step navigation that lets you customize all sorts of options for your conversion efforts such as destinations, checkpointing, logging options, auditing framework options, connection manager consolidation, converting child packages automatically associated with a package, etc. The nice thing about this is you can convert single packages or hundreds of packages at the same time! If you want to see some of this stuff in action you can watch the video here.

We converted the stuff and now we’re done right? NO WAY! With DTSxChange you also have the option of adding on our custom Auditing Framework which gives you tons of useful reports (Note: Use Report Viewer Application to view auditing data) so that you can easily and quickly view the state of your SSIS packages once their deployed. Here’s a listing of all of the different reports you get:

  • Which packages are currently running and which task is running inside the package
  • Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
  • Error and Warning by Task and Package
  • Run time by Task and Package
  • Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name, Data Flow Name, Connection String etc.).
  • Run time Trend for several days/weeks/years by Package and Task
  • Error/Warning/ Trend by Package and Task
  • Extract/Load Trend by Package, DataFlow
  • Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)

There are seven inbuilt reports are shipped this version and more will be added in the future release.

Report Name Description
SSIS Execution Dashboard This dashboard gives one place view of most common counters of SSIS package execution.
Package Execution Trend This report provides many useful information about trend of package runtime, task runtime and errors/warnings.
Errors/Warnings Provides detailed error/warning report including package level errors (package level errors not listed in any other report)
Recent Executions Provides information of running packages/tasks and already completed executions.
Running Packages Provides real time information of currently running packages and tasks.
Extract/Load Detail Provides extract/load detail (e.g. row count, source/target information, query, file name …) about each dataflow in each package execution.
Extract/Load Trend Provides graphical view of extract and load trend over several years, weeks, months and days.

DTSxChange helps you migrate away from those legacy DTS packages and gets you on to the latest SSIS platform in minutes as opposed to days or weeks! Don’t believe me, try it out yourself. When you download a trial version of our product it comes with 3 conversions for you to play with. Would you rather have one of the experts at Pragmatic Works do a live demo for you and your company? We can set that up as well for you by sending us a quick email. So what are you waiting for? Kiss DTS goodbye today!


Pragmatic Tools Week: BIxPress

tim-taylor-aus-tool-timeMuch like Tim “The Toolman” Taylor had his themed weeks, we’re going to do something similar. This week we salute: (play fanfare music here) the Pragmatic Works BI toolset. Today we’re going to talk about BIxPress.

I’ve previously posted on BIxPress and how it helps DBAs out, but the heart of this product is really aimed at making your development quick and easy. Today we’re going to focus on a few things that really make this tool worthwhile by looking at the top 3 features of this product that I really love.

Continue reading


SQL University: Precedence Constraints

Welcome back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the two in order to complete a unit of work. In today’s class we’re going to continue exploring that data load package and take a look at another critical piece of SSIS that you should know about: precedence constraints.

So what exactly are precedence constraints? They are the connectors that link together tasks in the Control Flow, and they define the workflow of your package. When two tasks are tied together with a constraint, the destination task will execute based on two things: The final state of the task that precedes it and special rules you can define on the constraint using specialized expressions.

Constraint Types

You can have different types of constraints between tasks. You can read more about constraints in detail from MSDN article (Link). I’ll briefly cover each of the constraint types in an abbreviated list and then we’ll discuss how we used these constraints in our parent-child package from our previous lesson.

  • Success – Workflow will proceed when the preceding container executes successfully. Visually indicated in control flow by a solid green line.
  • Failure – Workflow will proceed when the preceding container’s execution results in a failure. Visually indicated in control flow by a solid red line.
  • Completion – Workflow will proceed when the preceding container’s execution completes, regardless of success or failure. Visually indicated in control flow by a solid blue line.
  • Expression/Constraint with Logical AND – Workflow will proceed when specified expression and constraints evaluate to true. Visually indicated in control flow by a solid color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).
  • Expression/Constraint with Logical OR – Workflow will proceed when either the specified expression or the logical constraint (success/failure/completion) evaluates to true. Visually indicated in control flow by a dotted color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).


Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip.


Constraints in Action

Now that we’ve seen the different constraint types, let’s examine how they’re used in conjunction with parent-child package setup. Our first use of constraints comes at the top of the child package from the script task to the sequence containers. We’ve used an empty script task as an “anchor” task. This is used as a starting point to continue on to the corresponding workflow. As we learned in our last class, we have a variable being passed from our parent package with the value of the name of the file we are currently processing.

In this first example, we’re going to look at the constraint leading to the sequence containers for the Supplier table. We’ve used and Expression and Constraint here and chosen the value for Success. We’re also looking at the value of the variable being passed to the child package. For this particular workflow, we are waiting until the value of the variable ‘Parent_TblName’ is set to “supplier”. Once both of these situations evaluate as being true, we will execute this container.


Now that we’re inside our sequence container, we have another set of constraints. Once again we’re using an empty script task as an “anchor” for our precedence constraints. This time we’ve got two possible paths to go down. The first is to execute an Execute SQL task. This task checks for the existence of the table (in this case, the supplier table). If it exists it will drop the table and then recreate it. The other path leads directly to a data flow task which simply loads the table specified from the flat file.

I’ve created another variable on this package called ‘AppendFlag’ which is a boolean value. The purpose of this flag is so that you can choose to load the tables with a fresh load from the flat file (the Execute SQL task path) or you can simply append an already existing table’s data with data (data flow path). The default value of the variable is false.

The first path to the Execute SQL task uses an expression and constraint which is looking at the value of the ‘AppendFlag’ variable. In order for us to go down this workflow both value of ‘AppendFlag’ must be false AND the previous component executed successfully. The other path from the script task leads directly to the data flow task which actually loads the table. For this path, I’ve set the precedence constraint to look for the value of ‘AppendFlag’ to be true. In this path, however, we’ve chosen to use a logical OR. The reason for this being that the Execute SQL task, once complete, also leads to the data flow task. Due to the data flow having two different input paths, we must use the logical OR (if you try to choose logical AND, BIDS will quickly yell at you).


When we bring it all together, we now have a parent-child package that passes variable values. These values are used to execute specific workflows based on the value of the variable passed. Precedence constraints are an extremely helpful and invaluable tool in your SSIS toolkit. Using precedence constraints can help you create very dynamic workflows within your packages.


SQL University: Parents Just Don’t Understand

Welcome to the second week of SSIS this semester at SQL University. Today we’re going to talk about the relationship between children and parents. Ever had communication issues with your kids when you ask them to complete a chore? When they’re done, wouldn’t it be nice if they always came back and let you know they took what you said, applied it, and completed the job? What does that have to do with SSIS? Read on and find out!
Continue reading

Masters BIverse

Be the Master of your Data Warehouse Universe at SQLRally!

Do you have a data warehouse initiative in your current organization and looking for a way to learn how to properly build and support it? Would you like to learn how to do this straight from the Masters of the BIverse themselves? Well you’re in luck! Next month at SQLRally there’s going to be a great pre-conference session held by the following:

Mike Davis (Blog | Twitter)

Devin Knight (Blog | Twitter)

Adam Jorgensen (Blog | Twitter)

Patrick LeBlanc (Blog | Twitter)

In this full-day workshop, you’ll learn from the author team of Mike Davis, Adam Jorgensen, Devin Knight, and Patrick LeBlanc how to build a data warehouse for your company and support it with the Microsoft business intelligence platform. We’ll start with how to design and data model a data warehouse including the system preparation. Then, we’ll jump into loading a data warehouse with SSIS. After SSIS, you’re ready to roll the data up and provide the slice and dice reporting with SSAS. The team will walk through cube development and data enrichment with things like key performance indicators, which are essential for your future dashboards.  Lastly, we will cover how to report against the data warehouse with SSRS, including a primer in how to write MDX queries against the SSAS cube.

What you can expect to take away from this session:

  1. Practical knowledge of building a Dimensional Model
  2. Designing a simple ETL process using SSIS
  3. Designing a Cube
  4. Designing simple SSRS Reports
  5. Building an integrated process that fully leverages the entire MS BI stack to load a Data Warehouse.

You can register here and pre-con fee is $199 (which includes lunch). This is a great deal so what are you waiting for? Sign up today as slots are filling up fast! See you at SQLRally!


BIxPress 3.0: DBAs Welcome!

Much like the USA Network here in the States welcomes characters, I’d like to formally let the world know that BIxPress also welcomes folks, and this time it’s looking at you DBAs out there!

You may be thinking, “But Jorge, the product is called BIxpress, why as a DBA would I give a flip about it?!?” Glad you asked! I’ve recently made the transition from a DBA to a BI consultant and as part of my learning process for learning the BI stack I decided to take a crack at creating an SSIS package that would take a bunch of video files from a conference, compare the file names to the actual session titles (files came down named with their session codes, not names) and rename the files according to their formal session titles. If you’re interested in that, I’ll be posting another blog post soon detailing how I did it as well as you’ll be able to download the package yourself and try it out!

Continue reading

Pragmatic Works logo

Pragmatic Works: The Transition

So if you know me via Twitter or my blog you probably know that as of about a month or two ago I joined the fantastic team at Pragmatic Works. This transition was rather significant for me as I would be moving from the world of administration to the development side of SQL Server in being a BI consultant. This series will be a kind of chronicle of my personal journey through the transition from a DBA (with a social media complex) to a BI developer in one of the top BI shops around.

Continue reading


Ascending Mount BI Stack: Basecamp

Not quite Mount Doom but...

As some of you may know, I’ve recently transitioned over from the fabulous life of being a production DBA to joining the ranks of the BI developers with Pragmatic Works. In the years I’ve been a DBA I’ve often took a quick look and learned stuff from the BI stack here and there simply because it was there and quite frankly it’s pretty cool. Now, as a BI developer, it’s time to buck up and ascend the face of the Microsoft BI stack!

Part of my transition from production DBA to BI developer is learning the stack from top to bottom. As I journey through the different challenges of getting familiar with this side of SQL Server I’ll bring you, my handful of readers, along for the ride. While I was a DBA I’ll admit the BI stack always kind of intimidated me so I always shied away from it. Hopefully this series of posts will help future DBA’s turned BI developer (or just DBA’s looking to learn the stack) identify with my challenges and learn along with me.

As with anything, you’ve gotta have the right tools for the job and with this journey we’ll have to pack accordingly. First up we’ll need a guide book to help traverse the mountain. The guidebook of choice for this trip will be Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer). Next there’s basic equipment we’ll be working with. I’ve created a virtual machine and loaded it with Windows 2008 Standard Edition, SQL Server 2008 R2 Enterprise Edition (Database Engine, SSIS, SSAS, SSRS), Sharepoint 2007. Wait did he say Sharepoint 2007? Yes, I’ll be installing the older version for few reasons. One, most companies are still on 2007 so it’s best to learn at the majority target. Two, I’ll be doing an upgrade at some point of the SharePoint instance to 2010 so as to get experience with that part and then playing with more goodies there.

In addition to those tools we’ll need some other supplies, mainly that being data. That’s where you fine folks come in! I’ve created a sample form so as to seed some flat files for some SSIS fun later. Every so often I’ll update the data dump file so that you guys can play along with the same sample data you’re generating! To get to the form simply click here and have fun. Fill it out as many times as you want, the more data the better!

This should be a fun journey to take and I hope you guys enjoy coming along for the ride!