Tuesday, July 29, 2014

Upgrading your SSIS Management Framework: Part 3

At this point, you understand the options for moving an SSIS framework to the latest version of SSIS, and you've upgraded the logging portion of the framework using a hybrid approach.  The final step in the framework upgrade is handling your configurations.  Let's walk through an existing configuration implementation and how you can upgrade it by combining your existing implementation with the standard SSIS framework.

Overview

A typical "old-school" configuration scheme is described in the SSIS PDS book or in this blog post here: http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html.  Starting in SSIS 2012, the configuration scheme uses environments and parameters when using the Project Deployment Model, as discussed here: http://msdn.microsoft.com/en-us/library/hh213290(v=sql.110).aspx.

In both scenarios, the core ideas in a configuration scheme are:

  1. Provide the ability to move packages through environments without having to touch the packages
  2. Provide one location where connection strings / variables are stored, so in case a value changes, you don't have to change the value in multiple places

Assumptions

To enable our hybrid approach, we will utilize the SSIS 2012+ catalog as our "master" version of the configuration values and modify the previous framework to use its data.  This example counts on the following assumptions.  If your system is different, you may need to make some modifications to the implementation described here.

  1. Assumption for the old framework: All configuration entries modify Variables, rather than Connections or other object types.
  2. Assumption for the new framework: You have a CommonConfigurations environment on each server that holds your values for your development, test, and production servers, as well as an environment for each package that would have its own package level values.

Configurations

To tie the two systems together, we will use the environments stored in the 2012+ catalog to pass to the earlier framework.  The earlier framework retrieves all of its information from a table called dbo.SSIS Configurations, so we can just replace that table with a view that points to the new catalog!

Start by renaming your old table to [SSIS Configurations Old]:

EXEC sp_rename 'dbo.SSIS Configurations', 'SSIS Configurations Old';
GO


Next, create a view named [SSIS Configurations] that reads from the SSIS catalog:



CREATE VIEW dbo.[SSIS Configurations]
AS
    SELECT CAST(e.name AS NVARCHAR(255)) as ConfigurationFilter
        , CAST(ev.value AS NVARCHAR(255)) AS ConfiguredValue
        , CAST('\Package.Variables[User::' + ev.name + '].Properties[Value]'
                AS NVARCHAR(255)) AS PackagePath
        , CAST(ev.type AS NVARCHAR(20)) AS ConfiguredValueType
    FROM [SSISDB].[catalog].[environment_variables] ev
    LEFT JOIN [SSISDB].[catalog].[environments] e
        ON ev.environment_id=e.environment_id
GO


Compare the output from SSIS Configurations and SSIS Configurations Old, and add any additional variables needed to the environment in the SSIS catalog.  From now on, you will add new configurations only to the new catalog.  You have one place to keep the "master" values and you only change them in one place!



Good luck!



Keep in mind that based on your framework implementation, not all of this may be applicable.  However, I hope that it gives you something to think about as you are evaluating your framework upgrade options!

Friday, June 27, 2014

Upgrading your SSIS Management Framework: Part 2

Based on Part 1 of Upgrading your SSIS Management Framework, you’ve decided to go with a hybrid approach for your framework.  The hybrid approach which will use some components of the custom framework (this post will use the framework provided in SSIS PDS, but the concepts are applicable to any custom framework) and also utilize the standard SSIS framework.  This allows you to tie your existing package ecosystem with the latest and greatest built-in framework. Let’s talk through an overview of what we’re going to do and then explain each of the steps needed to implement it.

Overview

When it comes down to it, we need to accomplish two main things for this hybrid approach: tie our logging tables together and tie our configuration tables together.  When it comes to logging, each system has its own important identifier (ID) that can get you to anything else in the system.  The important ID in the custom SSIS framework is the PackageLogID, and the important ID in the standard SSIS framework is the ServerExecutionID.  The work you need to do is to map these two executions together.  When it comes to configurations, you want to be able to have one place to modify your connection strings and common variables that will modify all of your packages.

Logging

Let’s start with logging.  As previously mentioned, our goal here is to the two logging systems together.  Since we don’t want to modify the standard SSIS framework (that would defeat the purpose of moving to that framework!), we’ll do our modifications in the existing framework.  However, we want to be sure not to change anything in the packages themselves because that would cause a lot of rework.  Fortunately, we can do this in the table and stored procedure that the framework utilizes.

Begin by adding a new column to your main table that contains package executions, such as:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'ServerExecutionID' AND [object_id] = OBJECT_ID(N'PackageLog'))
BEGIN
    ALTER TABLE [dbo].[PackageLog]
    ADD [ServerExecutionID] bigint NULL
END


Next, you will modify the stored procedure to populate the field you just added:



UPDATE dbo.PackageLog
SET ServerExecutionId = (
    SELECT MAX(execution_id) AS execution_id
    FROM SSISDB.catalog.executions
    WHERE status = 2 AND end_time IS NULL
        and package_name = @PackageName )
WHERE @PackageLogID = @PackageLogID


This will insert the ServerExecutionID from the standard framework into the custom framework.  Next, you can modify your reporting queries to utilize the new column.  For example, I modified the existing standard framework query for the “All Executions” report to include information from the old framework in the following query:



SELECT TOP(10)
    a.[execution_id],
    CAST(a.[start_time] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[start_time], ISNULL(a.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration
FROM [catalog].[executions] a
INNER JOIN [catalog].[executions] b ON
    a.[package_name] = b.[package_name] AND
    a.[project_name] = b.[project_name] AND
    a.[folder_name] = b.[folder_name]
WHERE b.[execution_id] = ? AND
    a.[status] = 7
UNION ALL
SELECT a.PackageLogID,
    CAST(a.[StartDateTime] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[StartDateTime], ISNULL(a.EndDateTime, SYSDATETIMEOFFSET())))/1000 AS duration
FROM SSIS_PDS.dbo.PackageLog a
INNER JOIN SSIS_PDS.dbo.PackageVersion b ON a.PackageVersionID=b.PackageVersionID
INNER JOIN SSIS_PDS.dbo.PackageVersion c ON b.PackageID=c.PackageID
INNER JOIN SSIS_PDS.dbo.PackageLog d ON c.PackageVersionID=d.PackageVersionID
WHERE d.ServerExecutionID = ?
     AND a.ServerExecutionID IS NULL
ORDER BY [start_time] DESC


In this way, you can see all executions together, and you can use similar queries to tie any executions from the old framework to the new one!



Configurations



Next week, we’ll look at configurations and how to manage those in both frameworks.

Tuesday, June 17, 2014

Upgrading your SSIS Management Framework: Part 1

Background

Before SQL Server 2012, SQL Server Integration Services (SSIS) had no built-in logging, auditing, and configuration framework.  All of the pieces were available to build your own, but everyone ended up doing that just a little bit differently.  Most of us consultants came up with our own variation to implement at client sites and ensure that all of those functions at that one client were the same.  I'm especially proud of the framework that Rushabh Mehta and I developed that is published in Microsoft SQL Server Integration Services: Problem, Design, Solution (SSIS PDS) and implemented by many others as well.

Along came SSIS 2012, when Microsoft realized this "multiple-different frameworks" spread was happening, and thought "how cool would it be if we could standardize the framework so ALL our clients have the same one".  This would not only reduce initial development time of the framework, but also ensure that upgrades and future maintenance would go smoothly. Coinciding with (or perhaps due to) this decision, Microsoft moved the execution of SSIS packages to run inside of SQL Server.  Having a consistent framework is wonderful, and I'm a big fan of using the consistent built-in framework.

Upgrade Options

However, what do you do if you've already implemented a custom framework, similar to one in SSIS PDS?  There are a couple of options:

  1. Move lock, stock, and barrel to the new framework. To do this, you would have to upgrade all existing packages, remove the components that logged to the framework, and change your configurations schema to use parameters.  This means that you would never have to worry about upgrading your framework again because Microsoft will take care of it.  On the other hand, you would lose ties to your existing log records.  If you have a small number of packages or you have only used a custom framework for a short amount of time, I would recommend this option.
  2. Stick with what you've got. This option is the most easy to implement, but provides the least amount of value.  The new SSIS framework contains much more logging than most of the custom frameworks, and when a package fails and you don't know why, mo' logging = mo' better.  I do not recommend this option.
  3. Use a hybrid approach, which the potential to phase out the custom framework in the future.  This option includes a little bit of up-front work, but will be maintainable (and hopefully enhanced!) in future SSIS versions.

Okay, let’s do it!

Next week, we'll look at how to implement the hybrid approach based on the PDS framework.

Tuesday, June 10, 2014

Where is my BI Development Tool?

DontPanic

"The development tool for SQL Server business intelligence packages is missing!" says almost every developer who installs SQL Server 2014. It's okay, don't panic.  Microsoft decided to separate the installer for the server and the installer for the development environment.  Unfortunately, the next statement by almost every developer who installs SQL Server 2014 is "There are MULTIPLE SQL Server Data Tools?!?".  Also not a problem, let's talk about the difference options and how to get the correct software on your machine.

Option 1 is SQL Server Data Tools (the original), which is a template for Visual Studio that allows you to create and store information on databases and database objects.  This tool is part of Visual Studio.  For more information, see: http://msdn.microsoft.com/en-us/data/hh297027.

Option 2/3 are SQL Server Data Tools - Business Intelligence, which contain the templates for SSIS, SSAS, and SSRS (the standard BI tools we are accustomed to). This tool is part of SQL Server.  You have two places to download this (depending on if you're developing for SSIS 2012 or SSIS 2014.  Here are those download links:

  1. Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012 (and SSIS 2012): http://www.microsoft.com/en-us/download/details.aspx?id=36843
  2. Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (and SSIS 2014): http://www.microsoft.com/en-us/download/details.aspx?id=42313

I do wonder why they are named the same.  Is it possible that Microsoft might merge these two products in the future?  This could be a great move for full data warehouse projects - designing your data layer and then populating it with ETL go hand-in-hand!  Pretty please?

Tuesday, June 3, 2014

Eek! Starting as a Regular Full-Time Employee

Well, I jumped into the career deep end last week.  I started my first-ever job as a regular full-time employee.  This week contained a mixture of emotions for me: scared, nervous, but overall - EXCITEMENT!  It's been a long road, but I'm very happy with where I've come and where I'm going.

I started with a consulting company right out of college.  Back then, graduating with a computer science degree pretty much meant you knew a little bit about a lot, but had no deep knowledge in any area.  So working on a little bit of everything to gain some knowledge in a consulting company seemed like a perfect fit!  I still remember during my interview, the interviewee said "You'll be assigned a project over the weekend, buy a book on the technology, and by the time you arrive on Monday, you need to be an expert".  While we all know that won't exactly work, I was able to learn SO much by hopping around and trying different technologies, industries, and systems.

While trying out different technologies, I fell in love with data warehousing.  To that end, I then tried my hand at my own business, still consulting, but with more training and mentoring.  I absolutely loved this kind of work, but wasn't a huge fan of the full-time travel or all of the paperwork ;)  I then went back to consulting for a couple of local companies, where I was able to work with many organizations with data, without the travel or paperwork.

So... after ten years of consulting, why did I give it up?  Truthfully, I'm ready for a change.  While it's fun to bounce from organization to organization, there's always that dichotomy between "the client" and "the consultant".  And I don't blame either side for thinking that way.  Consultants provide a great service for organizations that don't have the resources (time, people, money, etc.) to do it themselves.  At the end of the day, a consultant's number one duty is to their company, whether that is looking for additional sales or is eventually leaving the project to move onto the next.

All that being said, I'm off to start my new adventure!  I'm a data architect for a firm based in Richmond that handles chemicals and manufacturing.  I get to help them consolidate their data in a data warehouse and hopefully learn a bunch in the process... I also need to learn how to be a regular full-time employee.  Do you have any advice for me?

Thursday, January 3, 2013

Happy New Year Resolutions

imageHappy 2013! 2012 was a great year, and I hope that 2013 will be even better. To assist, I decided to create resolutions this year.  I don't usually create resolutions because I believe that they aren't maintainable.  Too often we hear of the gym membership that expired after a month due to disuse, the 20 pounds to lose that became 2 pounds, the bad habit that we were going to break... tomorrow…

But what I do like about new year resolutions is that the new year is a fresh start, a way to wipe the slate clean and wish for a better and brighter future.  To make sure my resolutions don't become those drift into the ether, I need specific tasks that will force me to accomplish this.

So with that optimistic outlook, my professional resolutions for 2013:

  • Blog twice a month. This will be my hardest resolution based on past history. ;)
  • Tasks:
    • Tell people I'm going to do this (DONE!)
    • Create an initial list of blog topics and dates - is there anything you want to read about?
    • Add task reminders to my RememberTheMilk list so that I don't forget
  • Learn more about PowerPivot, BISM, and DAX.
  • Tasks:
  • Learn more about Windows Azure SQL Reporting.  I haven't delved into the exciting world of Microsoft cloud options, and I want to be able to intelligently share the different options available to people.
  • Tasks:
    • Set up my own account to explore the options
    • Create a comparison chart of pros and cons for cloud versus traditional BI/reporting options
  • Explore EIM  integration of DQS, MDS, SSIS. Matt Masson delivers a great presentation that shows how to use these three products together to create an EIM infrastructure.  I believe the real power of these tools include using each product to create a full data ecosystem.
  • Tasks:
    • Find a realistic example and implement a solution
  • Employee Management Training. On the "soft skills" side, I need to learn more about management.  I've recently agreed to start taking on minions--er, I mean managing employees.  This is an entirely new skill for me.  Since it directly affects the development and career of others, I really don't want to screw it up.
  • Tasks:
    • Harvard ManageMentor training.  I am lucky to be part of a company that believes in training its employees by offering opportunities such as this.  I plan on utilizing this service this year
    • Read The First-Time Manager

I think that's enough to start 2013 off right.  What are your resolutions?
Jessica

Monday, November 5, 2012

PASS Summit 2012

For the fifth year in a row, I am excited to present at the PASS Summit in Seattle, WA.  The PASS Summit is the premier SQL Server conference in the United States for anyone interested in SQL Server, SQL Server Business Intelligence, and Microsoft data technologies.  I always learn something new when I attend, and I enjoy catching up with old friends.  If you’re a new friend that I haven’t met yet, please introduce yourself to me at one of the following locations:

11/5/2012 8:30am-4:30pm SSIS Design Patterns pre-conference session

11/5/2012 6:00pm-9:00pm Networking Dinner at Gordon Biersch

11/6/2012 6:30pm-8:00pm PASS Summit 2012 Welcome Reception

11/7/2012 11:30am-12:30pm Book Signing – PASS Bookstore

11/8/2012 1:00pm-2:00pm & 3:00pm-4:00pm Book Signing – Apress Booth

11/9/2012 9:45am-11:00am Jessica’s Session: “Getting Reports on Your Schedule”

11/9/2012 11:30am-12:30pm Birds of a Feather table: Data Warehousing

Hope to see you then!