Jessica M. Moss

Hello, SQL Server 2016 Yesterday, Microsoft announced the release of SQL Server 2016 on June 1st of this year: https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/ .  Along with performance benchmarks and a description of the new functionality, came the announcement of editions and features for the next release. Good-bye, Business Intelligence Edition The biggest surprise to me was the removal of the Business Intelligence edition that was initially introduced in SQL Server 2012.  Truthfully, it never seemed to fit in the environments where I worked, so I guess it makes sense.  Hopefully, fewer licensing options will make it easier for people to understand their licensing and pick the edition that works best for them. Feature Comparison Overall, the business intelligence services features included with each edition for SQL Server 2016 are fairly similar to SQL Server 2014.  Nothing has been "...

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: Provide the ability to move packages through environments without ...

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

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

" 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 to...

Thank you to everyone who attended Michelle Ufford's and my presentation on SSIS Design Patterns for the 24 Hours of PASS session.  Michelle's demo materials can be found here: http://sqlfool.com/summit2012/ .  My demo materials for a 2012 template and some reporting can be found here: http://www.jessicammoss.com/Demo/24HOP_Demos.zip . If you missed it, the session recording is available here: http://www.sqlpass.org/24hours/fall2012/ . And if you want to see more design patterns, come see us in Seattle for a full day training session: http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=3159 . Enjoy!

A book, a pre-conference training session, and a webinar - oh my! The Book Over the past two years, I have been lucky enough to work with some of the great SSIS-gurus, Andy Leonard , Matt Masson , Tim Mitchell , and Michelle Ufford , on a book project that has finally been realized.  I am pleased to announce the publication of SQL Server 2012 Integration Services Design Patterns ( amazon | apress ).  Thank you to everyone who helped with the writing, editing, and reviewing of the book. The Pre-conference Training Session The PASS Summit has invited the ENTIRE author team to give a pre-conference training session on Monday, November 5, 2012.  This training session gives you the chance to ask anything you want from the author team and take home advanced knowledge that can immediately be implemented.  SSIS Design Patterns description: In this full-day session, the "SSIS Design Patterns" (Apress, 2012) author team – Matt Masson, Tim Mitchell, Jessica M. Moss,...

Thank you to everyone who attended my School of Wrox presentation "Upgrading and Overhauling Your SSIS Packages for 2012" last month.  There were many remaining questions at the end of the session that we weren't able to fit.  The questions below are the unaddressed ones asked during the session that are specific to upgrading to 2012.  Enjoy! Will SSIS 2008 package run AS IS in 2012? Yes, the SSIS 2012 service will temporarily convert the package in its 2005 or 2008 format to the 2012 format when the package runs.  The package may not execute properly if it references providers or assemblies that are not available, so upgrading the package before deploying is highly recommended.  For more information, see: http://msdn.microsoft.com/en-us/library/bb522577.aspx . Can we integrate file based execution in catalog in DB and generate reports about the executions? Storing packages for execution on the file system is only possible in the Package Deploy...

In an ideal world, the source system of your data warehouse has the exact information that you need to populate all of the fields in your dimensions.  In a less than ideal world (also known as the real world), we need to cobble pieces of data together.  You may come across one scenario where all of your dimension and fact information is in one large table.  How do we handle this ETL in SQL Server Integration Services? It is possible to load new records into your dimensions while loading your fact.  One way would be to use a Lookup transformation to check for existence, and if the business key doesn't yet exist, insert that value, return the surrogate key, and go along your merry way.  On the other hand, if you need to use those dimensions for other fact tables, you may decide to load only your dimensions first. To load all dimensions from one table, we can utilize the Aggregate transformation in SSIS, which provides the capability to perform aggregations on ...

When building a SQL Server Integration Services package, many business scenarios call for inserting a record into a table if it doesn't already exist. The most commonly used database destination, the OLE DB destination, looks as though it can handle this through the SQL command Data access mode. Unfortunately, SSIS appearances can be deceiving... In the OLE DB Destination, setting the Data access mode to SQL command causes a SQL command text window to appear. You can perform typical SSIS SQL statement actions, such as building the query through the Graphical Query Designer, importing the SQL from an external file, or parsing the query. Note that there is no option to specify query parameters on this display. You will not need to set query parameters because the OLE DB Destination uses this SQL statement to find the metadata of the desired insertion table. Even adding a WHERE clause to filter the data will not change the outcome of the result. All rows passed through the D...

When designing a SQL Server Integration Services package, it can seem tedious to drag over each and every task and component from the toolbox to your Control Flow and Data Flow and connect all of the precedence constraints and pipelines. You can alleviate some of this by modifying the default Business Intelligence options within Business Intelligence Development Studio (BIDS). Under the default settings, you can double-click any toolbox item and it will show up in your package designer with no connectors or specific place. To improve this, open up the Tools > Options... menu in BIDS. Then expand Business Intelligence Designers and Integration Services Designers. You will see Control Flow Auto Connect and Data Flow Auto Connect. If you check the option to "Connect a new shape to the selected shape by default", the drop down lists for specifying connector type and location are enabled in each menu. I prefer to use a Success constraint and add the new shape to the right of th...

Tom, an attendee at last weekend’s SQLSaturday Olympia , asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package. First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies . This will need to be done on any machine where you plan on running this package. Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name): Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim excel...

Extensibility in SQL Server Integration Services and SQL Server Reporting Services is achieved by writing custom code inside your package or report. SSIS provides a great interface by using Visual Studio for Applications (2005) or Visual Studio Tools for Applications (2008), lightweight versions of the Visual Studio IDE. When you create a script task or component, you can write code using intellisense and error squiggles. SSRS, on the other hand, does not provide any IDE for writing custom code. If you want to create VB functions, you have a notepad-like window without colors, intellisense, or any error handling. In comes SnippetCompiler, an application that allows you to write and compile snippets of code. You can download the executable here: http://www.sliver.com/dotnet/SnippetCompiler/ . The current version allows code in both VB.NET 2.0 and C# 2.0. A .NET 3.5 version is in Alpha release and can be downloaded from the same location. Keep in mind that if you minimize the app...

Expressions in SQL Server Integration Services packages allow you to dynamically change values during the execution of a package. Each time you create an expression, it's as though you're creating a little program to retrieve the desired value. One use of an expression is to split a value into chunks that can be configured and used in multiple places. A common example is separating out file information into a file path and a file name, where the file path is the reusable piece. The file path can then be managed through a configuration and modified at any time. The expression usually looks similar to this:      @[User::FilePath] + @[User::FileName] or maybe this:      @[User::FilePath] + "\\" + @[User::FileName] The obvious problem is that you need to remember to add (or not add) the back slash on the file path. By extending our "little program", we can handle either situation at the same time, like so:      @[User::FilePath] + ...

As well as adding new components and performance improvements to SQL Server Integration Services 2008, the team made some UI changes to the existing components to help increase developer productivity. It's the small things that can really make a difference when you're building your packages! Here are a few examples of the additions: Script Transformation The "Script" menu is now the first/default menu that opens up when you select Edit. So rather than pushing two buttons to actually get to the meat, you only have to push one. Gone are the days of having to remember the exact name, spelling, case, and namespace of your variables. Now there is a window that allows you to select which variables you want to lock for reading or writing. Row Count Transformation Another variable drop-down! OLE DB Source When using the Data Access mode: "SQL Command from variable", the value of the variable is now displayed beneath the variable name. No more digging in the Variable...

One of the new features of SQL Server Integration Services 2008 is the parallelization of the pipeline. This is implemented by creating multiple execution threads that can be run on different processors, increasing the performance of the package. In SSIS 2005, this separation of threads could be emulated by using a Union All transformation to create a new buffer. If you turn on logging in the data flow, you can see these separate execution trees in the events PipelineExecutionPlan and PipelineExecutionTrees. I created a simple package that creates a row set of integers, multicasts to a conditional split (as a terminator) and a flat file destination. Here are the messages from those events: User:PipelineExecutionTrees Begin Path 0 output "Output 0" (32); component "Script Component" (29)   input "Multicast Input 1" (53); component "Multicast" (52)   Begin Subpath 0     output "Multicast Output 1" (54); component "Multicast" ...

I've gotten several requests to put down in writing the configuration schema that I use as the base of my SQL Server Integration Services framework. It contains a set of configurations: an indirect environment variable configuration, which points to an XML configuration file configuration, which points to a SQL Server configuration. I learned this configuration from the Project REAL Reference Implementation . If you're getting started with a BI implementation, I highly recommend that you download it for some great ideas and best practices. Steps to implement: 1) Create an environment variable on your machine with the name of SSIS_CONFIG_FILE and the value of: C:\SSIS\Config\MasterConfigFile.dtsConfig. 2) Create an SSIS configuration file at C:\ SSIS\Config\MasterConfigFile.dtsConfig with the line: <configuration valuetype="String" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" configuredtype="Property"><configuredv...

At my last client, Rushabh Mehta and I created some custom components to handle delimited strings in a dimension. Here is a description of the components . We have since cleaned up and released the code through CodePlex . You can download the code from the following sites: SortDeDuplicateDelimitedString UnPivotDelimitedString Take a look at the projects and let me know if you have any comments, suggestions for improvement, or if you find them useful!

When building an Integration Services custom component, you inherit from the class Microsoft.SqlServer.Dts.Pipeline.PipelineComponent. You can override any of the methods within this class to add your own functionality. If you want to add a custom property to the component, you will need to override the ProvideComponentProperties subroutine. For example, if you want to add a custom property that will act as a switch for removing or including empty strings, you would use code similar to the following: Dim RemoveEmptyStringsProperty As IDTSCustomProperty90 RemoveEmptyStringsProperty = ComponentMetaData.CustomPropertyCollection.[New] RemoveEmptyStringsProperty.Name = "RemoveEmptyStrings" RemoveEmptyStringsProperty.Description = "Boolean that determines if empty strings should be removed. Value should be True or False." RemoveEmptyStringsProperty.Value = "False" 'Default value Then in your execution logic (in the ProcessInput subroutine), you would use ...

While working with a client this past week, I ran into a scenario where I needed to compare two dates in a SSIS package. One date would be the same for all rows, and one date would be different for each row. There are two possible implementations: include the "same date" in all rows and do a column comparison, or put the "same date" into a variable and do a comparison between the variable and column. My first instinct was that the column comparison would be faster because the extra trip to the server would be more expensive than extra column space. As I thought about it more, I realized that as the size of the dataset increases, the time to handle the extra column might overtake the hit from the extra trip. So I ran a test to find out! Scenario: I created a temp table in the AdventureWorksDW database called FactLottaInternetSales that contains multiple copies of the FactInternetSales database, but with all orderdatekeys set to 1. I created two packages to do the com...