I ran across an interesting behavior in SSIS this week, and thought it was worth sharing. Occasionally, I’ll have the need to use the value of a variable (set in the control flow) in the data flow. The typical way to accomplish this is to use a Derived Column transformation to introduce the variable’s value into the data flow. However, you need to be cautious if you are doing this with a String variable.

When you are working with Derived Column expressions, the output type of the Derived Column expression is based on the input values. For example, inputting an expression like 1+2 will result in an output column data type of DT_I4 (an integer). Basically, the editor guesses the data type based on the input values. Generally, this is OK – if you are referencing other data flow columns, or variables that aren’t of type string, the lengths of the values are static. However, when the editor calculates the length of string variables, it uses the current value in the variable. So, if your variable has 3 characters, the output data type only expects three characters. If the value in the variable is static and never changes, this is fine. But if the value is updated regularly (perhaps it’s a variable in a template package) or the value is set through an expression (so that the value changes at runtime), this can cause truncation errors in the package.

I created a sample package that demonstrates this. The package is pretty simple: a single Data Flow task, with an OLE DB Source, a Derived Column transformation, and a Union All (just there to terminate the data flow).

There is a single variable named TestVar that contains the value “1234”.

In the Derived Column transformation, there is a single new column added. Note that the calculated length for the output column is 4, matching the value “1234” in the variable.

If the package is run right now, everything works fine. But that’s not the case if you change the value in the variable to “123456”.

Running the package after changing the value results in the following error:

[Derived Column [91]] Error: The "component "Derived Column" (91)" failed because truncation occurred, and the truncation row disposition on "output column "ColumnFromStringVar" (289)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This occurs because the metadata in the Derived Column transformation isn’t updated when the variable’s value is changed. So, to avoid seeing this error occur in your packages, you need to explicitly set the output column’s length.

In 2005, you could change the calculated data type by editing the data type, length, precision, and scale fields. In 2008, however, the values are locked. You can change the data type by going into the advanced editor for the Derived Column. However, it’s easier to simply cast the string value, to force the Derived Column editor to treat it as if it has constant length.

By using this approach, as long as your string variable’s value is less than 50 characters, the Derived Column will continue to work. It’s best to set the length of the cast to the same value as the destination column’s length.

There’s a Connect submission on improving this behavior, either by updating the pipeline’s metadata as the string variable’s value changes, or by throwing a validation warning or error if the current value of the variable exceeds the output length in the Derived Column transformation. If you agree that this could use some improvement, you can vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=470995

In the meantime, I’d highly recommend performing an explicit cast in any Derived Column expression that uses a string variable, particularly if the value is subject to change.

The sample package is on my SkyDrive, if you’d like to see the error in action.

There are several methods available for deploying Analysis Services databases once you’ve build your solution, including direct connections, generating XMLA to deploy, and using the Deployment Wizard with answer files.  Still, building and deploying AS Databases can sometimes be a challenge in enterprise development scenarios.  One common scenario is when you have multiple developers working on a single solution where all the files are under source control.  I wrote a blog about about SQL Server Analysis Services Projects with Multiple Developers recently, and this is one of the issues that you tend to run into.  I created a sample you can use to help with this problem.  I’m not going to go over how to deploy AS solutions (it’s documented plenty of other places)… this is just a tool to address this particular scenario.

I create the SsasHelper sample demonstrate the functionality, and posted it to the Microsoft SQL Server Community Samples:  Analysis Services site on CodePlex.  I posted about this functionality in my blog SQL Server Analsysis Services ‘Project Helper’ if you want details on the mechanics.

Background

So you’ve built your solution, tested it, and everything works great.  All of your developers are hard at work constantly improving your OLAP capabilities, and they’re storing all of their changes under source control.  Now you need to do routine deployments to multiple environments.  One of the problems you might have run into is that you have to use Visual Studio to actually do a deployment.  Even if you’re deploying via the Deployment Wizard (using answer files), you still need to generate the .ASDatabase by building the solution.  This problem becomes a little bigger if you want to use a Build Server, that doesn’t have Visual Studio, where you compile your solution, test, deploy it, etc.  Currently, the only option you really have for this scenario is to make sure every developer Builds the solution after all changes and checks in a new .ASDatabase file.  This is a bit of a pain, and tends to lead to (at least occasional) issues with deploying updated versions.

SSAS Helper

I created a class library to work with Analysis Services projects.  This library has a couple of neat pieces of functionality, including being able to de-serialize a SSAS project to an AMO database, write an AMO database back out to component files, clean a AS project (removing volatile, non-essential fields… I went over it in SQL Server Analysis Services Projects with Multiple Developers), and creating a .ASDatabase file based on a project.  The library is written in C# 3.5, and is designed/tested with SSAS 2008 (though it should work with 2005… test it though).

Using SSAS Helper

If you’re running into this problem, you probably already have a good build solution, so I won’t go over that here.  What you’ll want to do is create a new build task (using whatever framework you’re using) using this component.  As an example, you can create a custom MSBuild using this library that takes the SSAS Project as an input, and delivers a .ASDatabase file as an output.  The task would then be added to the build file, so you will no longer have a dependency on Visual Studio to compile all your .cube, .dim, .role, etc. files into something that can be deployed.  I’ll try and post a sample in the next few weeks, but it shouldn’t be a major task.

Caveats

I built this tool and tested on my current project, and tested with Adventure WOrks… I haven’t seen any problems with it, but your mileage may vary.  Make sure you test with your project.

Here are some known issues for this functionality:

1. Partitions are reordered when De-Serialized/Serialized. Makes it a pain to validate, but I’ve seen no ill effects. Use the SortSssasFile functionality to make a copy of the files for easier comparison.
2. Some fields maintained for Visual Studio (State (Processed, Unprocessed), CreatedTimestamp, LastSchemaUpdate, LastProcessed, dwd:design-time-name, CurrentStorageMode) are lost when a project is De-Serialized/Serialized.

When I use this tool on a project, I work on a copy, and then do a file diff to check the output the first time.  I used Beyond Compare (http://www.scootersoftware.com/) to compare the entire directory, and each file side by side, just to make sure there are no unintended side affects.  I would recommend you do the same to make sure… this works fine on the projects I’ve used it on, but you need to make sure there’s nothing special about yours so you don’t accidently destroy something.

How it Works

This project works by de-serializing all the files referenced by the .dwproj file into AMO objects then serializing the entire database.  There is more detail on de-serializing/serializing the objects in my post SQL Server Analsysis Services ‘Project Helper’ .  The code is fairly well commented (well… I think it is 🙂 and should be fairly straight forward.

Next Steps

To use this, you’ll need create a build task in your framework of choice, and just plug it in to your solution.  If this proves difficult for people, I’ll try and provide a sample, but it should be fairly straight forward to do.  If it doesn’t work right out of the box, it should work with minimal modification.  Just make sure all of your project files (.dwproj, .cube, .dim, .role, .dsv, .ds, .dmm, etc.) are published and you have a way to push the .ASDatabase file to your target.

Conclusion

That’s about it… I hope this helps some folks out.  Let me know if you have any problems, and we’ll see what we can get working.

Cheers,

David

A topic that often comes up when discussing enterprise level development with SSAS is how to have multiple developers work on the same project at the same time.  This issue doesn’t come up for many installations… a lot of teams get away with just having a single person working on their OLAP capabilities.  However, for a decent sized implementation, you’re going to want to have more than one person working on the solution at the same time.  I’ll be discussing some of the issues, workarounds, and tools you can use to make concurrent SSAS development easier.

Here’s a link to the source code from later in the post if that’s all you’re looking for.

Background

Analysis Services objects (cubes, dimensions, roles, etc.) are manipulated either programmatically or in Visual Studio (Visual Studio is the normal method). These objects are persisted by serializing them to XML. When you deploy an Analysis Services database, you either connect directly to an instance of Analysis Services, or you save off XMLA scripts/files that can be used to create the database on a remote server.

If you have a single person working on an AS project, you don’t have a problem.  If you’re using source control with exclusive locks (i.e., only one person can edit a file at a given time) you can have multiple people working on the same solution, but not on the same object at the same time.  This is somewhat complicated by the fact that modifying one object (such as a dimension) may require a change in associated objects (such as a cube where it is included).  You’re still fairly limited in the amount of work you can do concurrently.

The way to have multiple developers working concurrently is to use source control with non-exclusive check-outs, so multiple people can work on each file at the same time.  The down side is that you eventually have to merge the copies each person is working on back together.  Since the SSAS files are large, complicated XML documents this isn’t necessarily an easy task.  Most source control systems will attempt to automatically merge non-conflicting changes, but this usually doesn’t work very well with SSAS files (for reasons I’ll go into in just a minute).  There are, however, some things we can do to make the task a bit easier.

Challenges with Merging SSAS Files

When SSAS objects are persisted to XML, they contain structural information about the objects (which is required) as well as environmental and formatting metadata (which can be helpful in Visual Studio, but is not required for the solution to work correctly when deployed). The environment and formatting metadata elements tend to be extremely volatile, and vary for each developer. Stripping the volatile fields from the XML files will make the merge process easier without affecting the cubes and dimensions that are deployed.

Ex. A developer checks out “Adventure Works.cube” , fixes an error in a Description field, then deploys the cube to test. When he checks the file in, he will have to merge large XML file. He has only changed one line, but large sections of the file will be different from copies checked out to other developers due to metadata capturing the state of Visual Studio and the local AS server. By stripping this metadata, the developer can focus on merging the one change that matters without having to verify every other change in the file.

SSAS Elements that can be Removed

The following represent the environment and formatting metadata elements that are persisted in Analysis Services files. These fields can all be safely stripped from Analysis Services files prior to merging to remove the large number of unimportant conflicts that normally occur.

Programmatically Removing SSAS Elements

I’ve create a PowerShell function ‘Clean-SsasProject’ that will iterate over all writable SSAS objects in a directory and remove the volatile elements by manipulating the XML.  The function will make a copy of every file it modifies.  It is written using using PowerShell v2 CTP3, but should be easy to back port if you need to.  I’ve included a commented out section that will process the .ASDatabase file as well… this is used for a particular scenario on our team, just including it in case it is handy for anybody.  Use the $WhatIf and $Debug flags to know what the function will do before you do it for real.  This code is geared to the project I’m working on currently, and you may want to modify it to meet your precise needs. 

I would recommend creating a backup of your solution before you try this script, just in case.  I’ve been using this for awhile with no ill effects, but you could have a scenario I never dreamed about, so…

***DO THIS AT YOUR OWN RISK.  IT WORKS ON MY MACHINE.  ***

Consider comparing the cleaned XML side by side with the original to make sure this process works for you… it’s worked fine for every project I’ve used it on, but better safe than sorry.

You can download the source here.

Using Clean-SsasProject (for an individual)

I have my environment configured to load all files with the pattern ‘*Library.ps1’ when PowerShell loads via the following script in my ‘Profile.ps1’ file:

   1: $powerShellScriptsDirectory = “c:PowerShellScripts”

   2: if (!$powerShellScriptsDirectory.EndsWith(“”)) { $powerShellScriptsDirectory += “” }

   3:  

   4: Write-Host Welcome $Env:Username

   5:     

   6: foreach($filename in Get-ChildItem $powerShellScriptsDirectory* -Include “*Library.ps1”)

   7: {

   8:     & $filename 

   9: }

I store the .ps1 file with Clean-SsasProject and the other functions it depends on in my PowerShell scripts directory, so it’s loaded every time the PowerShell environment loads.  You can then just run ‘Clean-SsasProject’ from the PowerShell prompt.  I also have a .Cmd file in my path to automatically clean my normal SSAS project.  It just uses the following commands:

   1: SET SSAS_PROJECT_LOCATION=C:SourceMyProject

   2:  

   3: PowerShell -Command “Clean-SsasProject %SSAS_PROJECT_LOCATION%”

Running that command file will strip the volatile fields out of any file in the directory that is writable (i.e., checked-out of my source control system).

Using Clean-SsasProject (for a team)

This tool is designed to work when every team member does the following:

1. Check-out all files required for a change.  Remember that modifying one object may require that another object be updated, so make sure and check out all objects that can possibly be affected).


2. Make the change.


3. Clean the files.


4. Merge/Resolve conflicts.


5. Build project output (if required for your solution… I’ll be posting on how to easy project builds/deployments in a few days)


6. Check-in all files required for a change.

General Best Practices

There are some other general things you can do to make concurrent development a little bit easier (most of these go for software development in general, not just Analysis Services).  If you’ve attempted to have multiple developers work on a project, you’re probably doing all these things already.  Remember that it is always faster and easier not to have to merge when you don’t have to.

Do Separate AS Cubes and Databases by Subject Area

Including only related objects in a cube/database is a standard best practice. This approach avoids potential performance issues, increases manageability and maintainability, and improves the presentation and understandability for the end user. This design pattern also lessens the chance that multiple developers will need to be working on the same object at the same time.

Don’t Combine Unrelated Attributes in a Single Dimension

Including unrelated attributes in a single dimension causes problems with performance, maintainability, and general use of the solution. Including unrelated attributes also promotes conflicts by increasing the chance that developers working on unrelated areas will need to work on the same file.

Do Communicate and Schedule Work for Minimum Conflicts

Make sure to communicate with other developers to avoid working on the same objects when possible. If you need to work on the same object, ensure the design changes are compatible and that there is no way to optimize the work.

Major changes that will dramatically affect source merging should be performed with an exclusive lock on the file.

Ex. A developer wants to re-order the 200 calculated members in the calculate script. The developer should wait until everyone else has submitted their changes, then make the change and submit it.

Do Check-out late and Check-in Early

Minimize the time you keep AS files checked out. While it may take some time to develop new functionality for AS (modifying the source database, creating an ETL to load the database from a source system, etc.) the work in AS is typically fairly quick to do if properly designed and prepared for. Complete the design and other development before checking out the Analysis Services files.

Do Use Tools to Help Merge

Use a side-by-side differencing tool to compare and merge different versions of Analysis Services files. A good diffing tool will have features to make this operation significantly easier. Consider using a tool such as Beyond Compare for this task.  You can use this process to verify that Clean-SsasProject works for your solution the first time you it.

Next Steps

Modify the provided source/process to meet your needs and environment.  There is no 100% “right way” to handle development like this… everyone’s situation will be just a little bit different, and require a little bit of customization.  I’m just trying to give you the tools to make it a little bit easier.

Conclusion

That’s all there is.  If you use the tools, techniques, and approach above it should make developing Analysis Services solutions with multiple developers a bit easier for for you.  You’ll still have some of the headaches normally associated with this type of work, but hopefully you’ll have an easier time of it.

Cheers,

David

A recent post on the SSIS forums was asking about creating multiple rows in a text output file from a single row in the data flow. Given a set of rows like this:

the poster wanted this output in the text file:

Basically, the poster wanted a line break in the middle of a row of data, while keeping a line break at the end.

There are a couple of ways to accomplish this in SSIS. One way is the use of a script task to create the file, which gives you complete control over the format of the file. There’s also a couple of ways to do it directly in SSIS. The first way is to use a Multicast transform to create two copies of each row, perform some string concatenation, and then combine them using a Union All or a Merge.

The Derived Column transforms are used to put the multiple columns into a single column, so that a variable length record can be written to the flat file. The Sort transforms and the Merge combines the rows into the proper order, before sending them to a flat file.

The other option (and one that probably falls under the category of stupid SSIS tricks), is to hack the flat file connection manager a little bit. You can set the column delimiters so that a carriage return/linefeed is inserted in the middle of the row. However, this isn’t as simple as just choosing {CR}{LF} as the column delimiter. SSIS checks to make sure that none of the column delimiters are the same as the row delimiter. Why it does that check, I don’t know, given the way it parses flat files. Regardless, you have to work around it. So, you can simply select the column where you want to introduce the break, and set it’s delimiter to {CR}.

Then insert a new column immediately following that column, set the output width to 0, and set the column delimiter to {LF}.

Now the output will include a carriage return / linefeed between the columns.

The sample package for this is located here. It is SSIS 2008, but the concepts are the same for 2005.

I’ve uploaded the ‘Database Tuning Advisor Output Renamer’ at http://DtaOutputRenamer.codeplex.com/.

OK… so Friday marked the first day I’ve ever gotten sunburned while coding.  I had a little bit of free time while at an outdoor event, and whipped up a a little utility to help apply standards to to DTA recommendations.

I use the SQL Database Tuning Advisor (DTA) a lot to generate basic recommendations for indexes and statistics based on a workload.  In my team, we store all index and statistics creation scripts in .SQL files, which are then run as part of our deployments.  We use a standard naming convention for each of the objects to enhance the maintainability.

Last week I ran the DTA against a workload I generated based on running reports on some new schema… not surprisingly, quite a few recommendations were generated.  It occurred to me my time could be better spent doing something besides renaming 50 database objects based on their definitions.  I decided to write a small application to help change the default names (such as ‘_dta_index_SsasProcessingRunArchive_c_7_1677965054__K10’) to something a little more user friendly (like ‘IX_dbo_SsasProcessingRunArchive_ObjectType_EventClass_SessionID_I_StartTime’).  You’ll want to modify the application to match your local coding standards, but it should be pretty straight forward to do. 

This application only handles a few cases, but does cover Clustered/Non-Clustered Indexes (with and without INCLUDE columns) and Statistics.  It should be easy to extend it if you need to.  This app is just something I whipped up in an hour or two, so it isn’t the most robust thing ever created.

I created the following regex (remove the line breaks… I just used those for presentation) to capture the index name, table name, and column/include lists for the indexes:

   1: CREATEs(?<NonClustered>NON)?CLUSTEREDsINDEXs[(?<IndexName>.*?)].*?ONs

   2: [(?<Schema>.*?)].[(?<Table>.*?)].*?((?<ColumnList>.*?))s*?(?:INCLUDEs

   3: ((?<IncludeList>.*?)))??s*?(?:WITHs*?(.*?)s*?ONs[.*?])

I created the following regex (remove the line breaks… I just used those for presentation) to capture the statistics name, table name, and column list for the statistics:

   1: CREATEsSTATISTICSs[(?<StatisticsName>.*?)].*?ONs[(?<Schema>.*?)]

   2: .[(?<Table>.*?)].*?((?<ColumnList>.*?)

I then just update the object names with a new name created based column lists and such.  I also through in the functionality to strip ‘go’ statements from input.

Enjoy…

If you’ve done much work with SSIS, you’re probably aware that on opening a package in BIDS, SSIS validates all the objects in the package. This can cause the packages to open very slowly, particularly if it has connections to a database that is currently unavailable.

I recently needed to upgrade several older packages to SSIS 2008. Unfortunately, these packages were referencing a database that I no longer have access to. On top of that, there are a number of data flows in each package, all of which use this non-existent database. Opening a package in BIDS was taking more than 10 minutes, which is about 9 minutes and 55 seconds past the point where my patience runs out. Normally, this wouldn’t be that big of a deal. Once the project was opened, I would just set the Work Offline option (located under the SSIS menu in BIDS), which prevents the validation from running.

However, each package was in its own project (for reasons I won’t go into in this post, but primarily failure to plan ahead), so I was looking at a very slow and painful process to upgrade these packages.

Fortunately, there is a way to enable the Work Offline option prior to actually opening the project. Locate the BIDS *.user file associated with the project. For SSIS, this file should be located in the same folder as the project (.dtproj) file, and will have a filename like “<project name>.dtproj.user”. Open this file in Notepad, and you should see something like the following (it’s got a few additional tags in 2008, but the general format is the same):

<?xml version=“1.0” encoding=“utf-8”?>

<DataTransformationsUserConfiguration xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=“http://www.w3.org/2001/XMLSchema”>

  <Configurations>

    <Configuration>

      <Name>Development</Name>

      <Options>

        <UserIDs />

        <UserPasswords />

        <OfflineMode>false</OfflineMode>

      </Options>

    </Configuration>

  </Configurations>

</DataTransformationsUserConfiguration>

Locate the <OfflineMode> tag (in red above) and change the value from false to true. Now, when the project is opened, it will already be in Offline mode, so you won’t have to suffer through a lengthy validation process.

There is a free community event from SSWUG coming up on April 17th. For people interested in Analysis Services (and who isn’t?), you’ll be able to see a webcast from Donald Farmer on developing high performance cubes. If you haven’t heard Donald speak before, I highly recommend signing up. If you have heard him, you’ve probably already clicked the link to register.

If you are interested in attending the Business Intelligence vConference, but haven’t made up your mind yet, you can now preview some of the sessions. A preview of my session on automating SSIS is available, but please don’t let that scare you off. There is going to be a lot of good content. You can still use the code SPVJWESP09 to get $10 off the registration.

I use virtual machines a lot for development and testing.  I typically start with a sysprepped base image that I then initialize every time I need a new machine.  One issue is that SQL Server doesn’t know it has been sysprepped… if you execute

   1: SELECT @@SERVERNAME

You will get the name of the machine from when you installed SQL Server.

I use the following PowerShell script to reset the name of the local instance to the current name of the machine:

# Load Assemblies we need to access SMO

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”)

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”)

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlEnum”)

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.WmiEnum”)

$asm = [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”)

##############################################################################

# Description:

# Change the name SQL Server instance name (stored inside SQL Server) to the name

# of the machine.  When a machine is unboxed after being sysprepped, it will still

# use the original SQL Server name as the instance name for SQL Server

#

# Input:

#

# Output:

#

# Author: DDarden

# Date  : 200904030748

#

# Change History

# Date        Author          Description

# ——–    ————–  ————————————————-

#

###############################################################################

function global:Set-SqlServerInstanceName{

    Write “Renaming SQL Server Instance”

    $smo = ‘Microsoft.SqlServer.Management.Smo.’

    $server = new-object ($smo + ‘server’) .

    $database = $server.Databases[“master”]

    $mc = new-object ($smo + ‘WMI.ManagedComputer’) .
    $newServerName = $mc.Name
    $database.ExecuteNonQuery(“EXEC sp_dropserver @@SERVERNAME”)

    $database.ExecuteNonQuery(“EXEC sp_addserver ‘$newServerName’, ‘local'”)
    Write-Host “Renamed server to ‘$newServerName’`n”

}

# Set the SQL Server instance name to the current machine name

# MSSQLSERVER service needs to be restarted after this change

Set-SqlServerInstanceName