Agile BI

I’ve been a bit lax posting on my blogs and the MSDN forums recently. Fortunately, I have a good reason (at least I think it’s a good one). 🙂 My employer, Mariner, has graciously given me permission to open source a unit testing framework for SSIS packages. Preparing it for release has taken a bit more time than I expected, as I wanted to polish up a few items, and that led to a few more changes, etc. The framework, as we were using it, was definitely functional, but I wanted to make a few changes for ease of use. Now I have those changes in, and an alpha (but functional) version is available on Codeplex, under the ssisUnit project.

I’ve posted previously about unit testing for SSIS, and how I really missed the automated unit testing capability that I’d taken for granted in more traditional application development. Since the team currently has no plans for unit testing for SSIS, it seemed like a good time to get this out and available to the public. There are currently methods of testing SSIS, but most of them involve testing the package as a whole. One of our goals for unit testing SSIS, though, was to enable testing at a more granular level. ssisUnit enables testing down to the individual task level in the control flow. In future iterations, I’d like to expand the functionality to include testing individual components in the data flow.

We have additional plans for ssisUnit in the future, including Visual Studio integration, additional command capabilities, and a GUI for creating the test cases. The current version is v0.50, and I hope to have another release by mid-April. Please download it, give it a whirl, and provide feedback and suggestions for improvement. If you’re interested in contributing to the project, please leave a comment on this post, or email me at john.welch@mariner-usa.com.

SQL Server 2008 introduces a new task that allows you to profile the data in a table. Jamie Thomson has a great series of posts about the different profiling options available on the task. If you are not already familiar with the task, I highly recommend starting there, as I won’t be covering that information here. Instead, I’ll be focusing on a way to make the task a bit more dynamic.

Before I go any further, I need to mention that this article is based on CTP6, and the technique I’m describing may need to be updated to work in future versions. However, I’ll be talking with the SSIS team in April about improving the task so that this approach may be a bit easier.

I was pretty excited when the task appeared in CTP5, but I was a bit disappointed by the actual experience of using it. My biggest disappointment was that you to select individual tables to profile. I was really hoping to be able to point it to a database, select all the tables I wanted to profile, and let it run. Unfortunately, there did not appear to be a way to even set the table through an expression. There is a ProfileRequests property, but it is a collection, so you can’t set expressions on it.

After some poking and prodding, though, I found the ProfileInputXml property. This property is not visible in the Properties window in BIDS, but you can locate it in the expressions dialog for the task. This property holds a complete XML based description of the profile requests for the package. Since it is available to set through an expression, it opens up the possibility to make the task dynamic.

The first thing I had to do was to get a sample of the XML. You can get a copy of the XML by viewing the XML code for the package, locating the task, and grabbing the contents of the ProfileInputXml tags. Since this is XML contained within XML, all the typical tags have been converted to their escaped versions (< becomes “&gt;”, etc.). The other approach is to use the SSIS object model to extract the value directly from the property. Once I had the XML, it became a matter of replacing the appropriate bits of the XML to make it dynamic. I ended up with the following:

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

<DataProfile xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance 
   xmlns:xsd=http://www.w3.org/2001/XMLSchema 
   xmlns=“http://schemas.microsoft.com/sqlserver/2008/DataDebugger/”>

  <DataSources />

  <DataProfileInput>

    <ProfileMode>Exact</ProfileMode>

    <Timeout>0</Timeout>

    <Requests>

      <ColumnNullRatioProfileRequest ID=“NullRatioReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <Column IsWildCard=“true” />

      </ColumnNullRatioProfileRequest>

      <ColumnStatisticsProfileRequest ID=“StatisticsReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <Column IsWildCard=“true” />

      </ColumnStatisticsProfileRequest>

      <ColumnLengthDistributionProfileRequest ID=“LengthDistReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <Column IsWildCard=“true” />

        <IgnoreLeadingSpace>false</IgnoreLeadingSpace>

        <IgnoreTrailingSpace>true</IgnoreTrailingSpace>

      </ColumnLengthDistributionProfileRequest>

      <ColumnValueDistributionProfileRequest ID=“ValueDistReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <Column IsWildCard=“true” />

        <Option>FrequentValues</Option>

        <FrequentValueThreshold>0.001</FrequentValueThreshold>

      </ColumnValueDistributionProfileRequest>

      <ColumnPatternProfileRequest ID=“PatternReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <Column IsWildCard=“true” />

        <MaxNumberOfPatterns>10</MaxNumberOfPatterns>

        <PercentageDataCoverageDesired>95</PercentageDataCoverageDesired>

        <CaseSensitive>false</CaseSensitive>

        <Delimiters> \t\r\n</Delimiters>

        <Symbols>,.;:-“‘`~=&amp;/\\@!?()&lt;&gt;[]{}|#*^%</Symbols>

        <TagTableName />

      </ColumnPatternProfileRequest>

      <CandidateKeyProfileRequest ID=“KeyReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <KeyColumns>

          <Column IsWildCard=“true” />

        </KeyColumns>

        <ThresholdSetting>Specified</ThresholdSetting>

        <KeyStrengthThreshold>0.95</KeyStrengthThreshold>

        <VerifyOutputInFastMode>false</VerifyOutputInFastMode>

        <MaxNumberOfViolations>100</MaxNumberOfViolations>

      </CandidateKeyProfileRequest>

      <FunctionalDependencyProfileRequest ID=“FDReq”>

        <DataSourceID>DatabaseConn</DataSourceID>

        <Table Schema=“” + @[User::SchemaName] + “” Table=“” + @[User::TableName] + “” />

        <DeterminantColumns>

          <Column IsWildCard=“true” />

        </DeterminantColumns>

        <DependentColumn IsWildCard=“true” />

        <ThresholdSetting>Specified</ThresholdSetting>

        <FDStrengthThreshold>0.95</FDStrengthThreshold>

        <VerifyOutputInFastMode>false</VerifyOutputInFastMode>

        <MaxNumberOfViolations>100</MaxNumberOfViolations>

      </FunctionalDependencyProfileRequest>

    </Requests>

  </DataProfileInput>

  <DataProfileOutput>

    <Profiles />

  </DataProfileOutput>

</DataProfile>“

Note that this string has already had the quotes and special characters escaped (using the symbol) and two variables embedded in it. The User::SchemaName variable holds the schema name, and the User::TableName holds the table name. This XML is set through an expression on the ProfileInputXml property.

I created a simple package to iterate all the tables in a database, and run this task for each one.

The Execute SQL Task (Get List of Tables) retrieves the list of tables and their schemas as a recordset, and stores it in an Object variable. The For Each Loop (Loop through tables) iterates through the recordset, updating the table and schema variables with the current values. The Data Profiling Task is set to save each profile to a file in the C:Temp folder.

The XML above contains all of the request types for profiling, including the ColumnPatternProfileRequest. This one runs fairly slowly, so you may want to remove it before testing this. To take out a particular request, just delete that section of the XML, from the opening tag of the request to the closing tag (<ColumnPatternProfileRequest> to </ColumnPatternProfileRequest>). Also, I would recommend that you not run this against a production database without clearing it with someone first, as it generates a lot of database activity.

This gives you a way to easily profile an entire database, or even all the databases on a server, with a little enhancement. It also makes it more practical to run this against a database that you are not familiar with, in order to get a general feel for the data. Being a consultant, that is very valuable to me. Being able to do this goes a long way to reducing my concerns about the usability of this task.  I’m hoping that the user interface will be improved by RTM, but I also want to make sure it continues to support setting the requests dynamically.

I’ve posted the sample to my Skydrive here. Let me know if you run into any problems with it.

The February CTP for SQL Server 2008 has been released, and is available to download at https://connect.microsoft.com/SQLServer/. Microsoft is also sponsoring a bug bash contest, with prizes for the most bugs, most critical bug, etc. The rules are posted here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=7734.

I’ve posted about handling multiple errors in SSIS before, but that post was addressing it in the context of the control flow. What if you want to capture a series of data errors from the data flow? An example of this might be a requirement to capture and email all the rows that fail in a Lookup transform, so that the reference table can be updated with new values.

There are a number of ways to do this. You can send the error output of the Lookup to a Flat File destination, and then attach that to an email message using the Send Mail task. If you want to avoid writing anything to disk, you could send the error rows to a Recordset destination, and then use a Script Task to put them into a string variable to send. Or you could just write them directly to a string, using a Script destination.

I’ve put together a sample package that shows how to send the error rows to either a flat file or a string variable, and then how to email both. The control flow consists of a Data Flow task, and two Send Mail tasks (you won’t need both, they are just there for illustration purposes).

The data flow is fairly basic. The OLE DB Source uses a hard-coded query to return the numbers 1 through 5. The Lookup uses a hard-coded query to return a reference table with 1 and 2, so we expect 3, 4, and 5 to fail. The Row Count transform is used to capture the number of rows in error, so that we know if an email needs to be sent. The Multicast is used so that we can show both methods of handling the error rows. The Write Errors to File destination simply sends the error rows to a flat file.

The Write Errors to String Script Destination uses a StringBuilder class to capture each row, then writes it to the errorRows variable at the end of data flow processing. The StringBuilder is more efficient for handling large strings, which is why it is used here. If the package was processing a large number of rows, the error row string could get quite large.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Text

Public Class ScriptMain

    Inherits UserComponent

    Dim errorMessages As StringBuilder = New StringBuilder()

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        errorMessages.AppendLine(String.Format("Key value {0} not found in the reference table.", Row.KEYVALUE))

    End Sub

    Public Overrides Sub PostExecute()

        Dim vars As IDTSVariables90

        Me.VariableDispenser.LockOneForWrite("errorRows", vars)

        vars("errorRows").Value = errorMessages.ToString()

        vars.Unlock()

    End Sub

End Class

Back in the control flow, each of the Send Mail tasks has a precedence constraint that also has an expression set on it. The expression is just checking to see if there were any errors. If not, we don’t need to send the emails.

The Send Mail – No Attachment task uses the string variable populated by the Script destination in the data flow as the Message Source for the email.

The Send Mail – attachment, on the other hand, uses a file as an attachment to the message. There is something to point out with this method. The package has a string variable defined that holds the path and filename of the error file. This variable is used in an expression on the ConnectionString property of the flat file connection manager, and in an expression on the FileAttachments property of this Send Mail task. This lets us set the path in one place, and avoiding changing it in multiple locations if it ever needs to be updated.

That’s all there is to it. I’ve uploaded the sample package to my SkyDrive, located here. To run it, you’ll need to update the SMTP server address in the SMTP connection manager, and the ErrorFile variable to point to a valid path on your machine.

A common question with SSIS is how to handle adding headers and footers to a flat file when the format is different that the data rows. This gets more difficult when the header or footer needs to contain data based on the actual data rows, such as a total or a record count.

Basically, this can be accomplished by creating the header or footer in a separate task, and then prefixing or appending it to the data. I’ve prepared a sample package that shows a couple of different ways to accomplish this. For generating footers, it shows how to use a second data flow to append an additional row onto the footer. There are two methods shown for adding headers. Method 1 uses a Script task to add the header by loading the file into a string, adding the header, and saving the file again. Method 2 creates a separate header file, and uses a file copy operation to concatenate the two files.

There a couple of things to note about the package. There are three flat file connection managers defined in the package.

Two of the connection managers (Destination and DestinationWithFooter) point to the same file, but have different column definitions. All three have their connection strings set by expressions that point to variables. If you want to change the target files, you need to modify the variables, not the connection managers.

The DestinationFile variables holds the path to a working file, which will hold the data before the headers and footers are added. The DestinationHeader variable holds the path to a working file used only for method 2, which holds the header row before it is concatenated into the final file. The FinalFile variable holds the path to the final output file, which includes the header, data, and footer rows.

The Generate File data flow uses a Data Generator component from Konesans (a free download from www.sqlis.com). It simply generates some data, captures the number of rows using a Row Count transform, and outputs it to the Destination connection manager.

The Append Footer and Create a Header File data flows are very similar. They both use a Script source to generate a single row containing the footer or header data, respectively. The script is below.

Public Overrides Sub CreateNewOutputRows()

    Dim recordCount As Integer

    Dim vars As IDTSVariables90

    ‘Get the record count

    Me.VariableDispenser.LockOneForRead(“RecordCount”, vars)

    recordCount = CType(vars(“RecordCount”).Value, Integer)

    vars.Unlock()

    ‘Output one row with record count

    Output0Buffer.AddRow()

    Output0Buffer.FooterRow = String.Format(“Footer Row Count: {0}”, recordCount)

    Output0Buffer.SetEndOfRowset()

End Sub

The two differ in the way the destination is handled. In the Append Footer data flow, the Flat File destination is set to append to the file, rather than overwriting it. It uses the DestinationWithFooter connection manager, which points to the same file that the data was sent to in the Generate File data flow. The DestinationWithFooter connection manager defines the file as having a single column. That makes it easier to append the footer row directly to the file. After the Append Footer data flow task has run, there is a single file that has the data rows and a footer row.

In the Create a Header File data flow, the header row is sent to a new file. Then, in the Append Files execute process task, the header and the data file are concatenated together using a copy command. This is Method 2 for adding the header.

For Method 1, the Add Header script tasks uses the System.IO.File class and the System.Text.StringBuilder class to create a new file containing the header row and the data rows, which already has the footer included.

Public Sub Main()

    Dim fileContents As New StringBuilder()

    Dim vars As Variables

    Dim recordCount As Integer

    Dim finalFile As String

    ‘Get the record count

    Dts.VariableDispenser.LockForRead(“RecordCount”)

    Dts.VariableDispenser.LockForRead(“FinalFile”)

    Dts.VariableDispenser.GetVariables(vars)

    recordCount = CType(vars(“RecordCount”).Value, Integer)

    finalFile = CType(vars(“FinalFile”).Value, String)

    vars.Unlock()

    ‘Write header, then append file contents and write back out.

    fileContents.AppendLine(String.Format(“Header Row Count 1: {0}”, recordCount))

    fileContents.Append(File.ReadAllText(Dts.Connections(“Destination”).ConnectionString))

    File.WriteAllText(finalFile, fileContents.ToString())

    Dts.TaskResult = Dts.Results.Success

End Sub

Hopefully this example is helpful, if you are trying to sort through the options for adding headers and footers. The example is available on my Skydrive here.

Stuart Ozer on the SQL Server Customer Advisory Team (CAT) has posted a new tool to CodePlex to help with the management of "staging" tables for swapping data in and out of partitioned tables. Since this is a common scenario in data warehousing scenarios, I’m really looking forward to trying this out on my next project. If you are interested in it, you can see a description here, and download the code here.

Previously, I posted about how multiple errors in SSIS can be combined into a single unit for emailing or logging. Recently, a thread on the MSDN forums pointed out that the example wasn’t quite complete. While it does show how to collect all the error messages and get them into a single string, it doesn’t show how to email or log that string only once. So I’m giving it another shot, and show two options for handling this. This post does build on the previous one, so please read it before proceeding.

If you download the sample package in the previous post and examine it, you’ll see that there is a script task in the OnPostExecute event. This script task is where the error messages are assembled, and the code to deliver the bundled error message would be placed. Please note – the script does not actually email or log the message, that was left open for the reader to implement.

If you run the package, you’ll notice that the OnPostExecute event fires twice. It’s firing once for the data flow, and once for the package. Since the OnPostExecute event handler is where the script is located, this causes the message to be delivered twice – not what we want. To work around this, we need to make sure the OnPostExecute only fires once. One way to handle this is to set the Propagate system variable to false. The Propagate variable controls whether the event is raised to the next container. In this case, if Propagate is set to false on the data flow’s OnPostExecute event, the package’s OnPostExecute will only be fired once.

However, this approach requires that all tasks in the package need to have the Propagate variable set to false in their OnPostExecute event handlers. To work around this, I recommend incorporating a single Sequence Container, with the Propagate variable set to false on it’s OnPostExecute. Any tasks in the package should be added inside the Sequence Container. If you do this, the Propagate variable only needs to be set once.

Another approach is to eliminate the use of the OnPostExecute event altogether. To do this, the Script Task needs to be moved to the control flow, and the OnPostExecute event handler can be deleted altogether. A Sequence Container should still be used to hold all of the tasks that need to be grouped for error handling. The Script Task should be connected to the Sequence Container with a Failure constraint.

This approach also ensures that the Script Task will only execute once, if there is a failure in the Sequence Container.

Hopefully, this helps clarify how to leverage collecting the error messages to only send a single error result. I’ve uploaded the samples here, so please feel free to take a look at them and let me know if you have questions.

There’s a minor annoyance in SSIS with the way the diagram of the package is saved. If you’ve ever spent time working in a package with a lot of objects, you’ve probably encountered it. You move some objects around, save the package, close it, then reopen it. When the package is displayed, the objects are off center, or maybe don’t even show up. This is because the view is scrolled so that all the objects aren’t in the initial viewable area.

If you want a little background on what’s happening, read this paragraph. Otherwise, skip ahead to the next one. Each object in the diagram is saved with it’s x (Horizontal) and y (Vertical) coordinates persisted in the DTSX file. In addition, the view’s current Left and Top position is saved as well. These settings tell the IDE how to lay out the diagram, and how to initially position the view of it.

To reset the initial view, you need to alter the XML for the package directly. As with any direct editing of the XML, it’s not supported, and you should definitely make a backup of the package first. You need to alter the PersistedViewPortLeft and PersistedViewPortTop properties. Be aware, there may be multiple copies of these values, one set for the control flow, and one set for each data flow defined in the package. You can set these values to 0 in order to reset the view to a 0,0 top left corner.

The XML tags you are looking for look like:

<dwd:PersistedViewPortLeft>0</dwd:PersistedViewPortLeft>

They may also appear as escaped versions of the XML (not sure why, some of my packages do this, others display normally):

&lt;dwd:PersistedViewPortLeft&gt;0&lt;/dwd:PersistedViewPortLeft&gt;

Just change the value between the tags to set the property value.

I’ve been meaning to post this for a while, but real life keeps intruding. I did a presentation on SSIS performance for the Charlotte SQL Server User Group at the end of November. It went well, though I was trying to squeeze a 90 minute presentation into 60 minutes, so I had to skim over some of the good stuff. You can find the presentation here (it’s the first link, at least till we have another meeting).

One item that I did need for the demos in the presentation was the ability to slow down the data flow. Normally, SSIS processes so fast that you can’t really see what’s happening. I found a few examples of script components that introduced a delay every time a buffer was processed. Unfortunately, that wasn’t flexible enough for me, as part of my demo was adjusting the numbers of rows per buffer. I really wanted something that would give a delay every X rows, while being easy to configure without having to edit the script each time. I ended up writing the following code:

Public Class ScriptMain
    Inherits UserComponent

    Private RowCount As Integer
    Private rowDelay As Integer
    Private everyNRows As Integer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        Dim vars As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90
        Me.VariableDispenser.LockForRead("EveryNRows")
        Me.VariableDispenser.LockForRead("MSTimeDelay")
        Me.VariableDispenser.GetVariables(vars)
        rowDelay = CType(vars("MSTimeDelay").Value, Integer)
        everyNRows = CType(vars("EveryNRows").Value, Integer)
        vars.Unlock()
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        RowCount += 1

        If RowCount Mod everyNRows = 0 Then
            System.Threading.Thread.Sleep(rowDelay)
        End If
    End Sub
End Class

This relies on two integer variables to be declared in the package. EveryNRows specifies how many rows should be processed before introducing a delay. MSTimeDelay sets how long the the delay should be, in milliseconds.

One thing to be aware of – in the above script, I am reading the package variables in the PreExecute method and storing them in instance variables. Locking and unlocking the variables is time consuming, and will drastically slow things down if you do it in the ProcessInputRow method.