Last week I presented the session “” at the PASS 2013 Summit.
Here’s a link to the demo that I went through.
Last week I presented the session “” at the PASS 2013 Summit.
Here’s a link to the demo that I went through.
I did a webinar this week for Pragmatic Works’ “Free Training on the T’s”. The topic was “Unit Tests for SSIS Packages”. If you attended, thanks for taking the time! If not, the recording is now available at the link above. You can also download the slides from my SkyDrive, and ssisUnit can be downloaded from CodePlex.
We had great turnout for the session, and a lot of great questions. I didn’t have time to address all of them during the webinar, and I had a number of requests to share my answers with all the attendees, so I thought I’d write up a blog post on the questions that I didn’t get a chance to answer.
“For test drive development, should a “final test” (of sorts) be performed after the “refactor” phase to ensure that the act of refactoring didn’t negatively alter the code?”
Yes, absolutely. You want to run your tests after any code changes. This helps ensure that code continues working as expected, and that you don’t have hidden “side effects” associated with your changes.
“How would you unit test a package that is performing transforms in the source query?”
I would create a known set of data in a test version of the source database. I would then create a unit test for the data flow task that checks that the output includes the data with the proper transformations. This will get much easier in a future release, when it will support data flow component testing in the unit test harness, allowing you to test the results of a source component directly.
“What is the best way to incorporate SSIS testing on existing packages to automate..as this also needs requirement breakdown?”
I approach this in the same way that I approach adding tests to an existing .NET application. Start by identifying a subset of existing packages to work with, and add tests to those. Once those have adequate coverage, move on to the next set. As far as requirements breakdown – yes, you do need to understand what the package is supposed to do in order to test it properly. You can add some simple test cases without a great deal of analysis (for example, did the task execute successfully?) but to get real value out of the tests, they do need to check that the task carries out the requirements as intended.
There is the option of generating unit tests automatically for existing packages via the ssisUnit API. While this can improve code coverage, I would caution you not to rely on it to verify real functionality.
“Sorry I missed the first part of the talk. so not sure if you already talked about this. But how do you recommend creating test data for dimensions if you need to use synthetic data? Are there any tools you recommend using?”
Remember that unit and integration testing is more about verifying functionality, and less about performance. So I like to create a small amount of handcrafted test data that hits the specific scenarios that I need to validate. I find that most of the tools out there for generating data tend to work well if I want large volumes of test data, but not so well for concrete examples. For example, when I need to validate that the package handles a Type 2 SCD update that affects multiple rows correctly, I need 3 to 5 rows of test data that are very specific. Data generators don’t do that very well.
“Can we automate the ssisUnit with build & deployments in TFS?” and “How would you enable automated testing with ssisUnit for Continuous Integration?”
The simplest way to to incorporate ssisUnit into your builds is by calling the command line test execution tool. It’s called ssisUnitTestRunner2008.exe (substitute the appropriate version number for your version of SSIS) and you can find it in the folder where you installed ssisUnit.
Another approach is to use the ssisUnit API, but this requires some level of .NET or PowerShell coding.
“Is ssisUnit backward compatible with VS2008?” and “Is this framework compatible with MS SQL Server 2012?”
It does work against 2008 and 2012. You can download the 2008 version directly from the CodePlex site. For 2012, you need to download and compile the source currently, which can be done with the free Express version of Visual Studio. The next release will have separate setups for each version.
“Does the SSIS unit testing work with evaluation edition of BIDS 2008 R2?”
Yes, it does.
“Will this connect with the Microsoft Parallel Data Warehouse?”
I have not tested this myself. However, because it uses standard OLEDB and ADO.NET connection technology for database access, I don’t see any reason why it wouldn’t work.
“I am a SQL 2008 R2 user. What version of VS is John running? What were those testing menu options?”
I was running SQL 2012 for the demo. The menu option for running ssisUnit was created using the External Tools menu option under the Tools menu in VS.
“I’m lost. How did you set up the tests using the GUI and then link it to the SSIS package? Did i miss that?”
During the demo, I showed a prebuilt ssisUnit test, that I opened in the ssisUnit UI tool. The unit tests are linked to packages through Package Reference objects, which basically refer to a package by it’s file path, or location in SQL Server.
“Can you use a different operator then equal to? Like greater than, less than, different than?”
Yes, the Asserts in ssisUnit can use expressions. These are C#-based expressions that evaluate to True or False. You can use an expression like “((DateTime)result).Date==DateTime.Now.Date” to check that the result of the Assert command is equal to today’s date. “result”, in the expression, represents the object returned by the command associated with the Assert. You can apply pretty much any C# / .NET operation to the result object. For more examples, check out this page.
“When performing SCD operations, the details of the row need to be inspected rather than just count of rows. How would you test this? Within ssisUnit or another tool?”
Well, I’d use ssisUnit, but I’m not exactly unbiased . Currently, you can do this using the SQL Command, which enables you to retrieve specific row details from the database. In the future, you will be able to do this more directly by testing it in the data flow.
“Did you say the file command can accommodate various file types such as xml, csv, pipe delimited, etc.?”
I didn’t say that (I don’t think), but it will work with pretty much any file. Internally, it uses standard .NET file operations, so it’s very similar to the capabilities of the File System task in SSIS. It doesn’t actually care what format the file is in, it can copy, move, and delete it regardless. For line counts, it counts the number of carriage return / line feeds in the file.
“Do you have a guide on how to setup ssisUnit with SSIS? We have tried using this before, but we couldn’t successfully run the test.”
I’m sorry to hear you had problems with it. The Getting Started guide is admittedly light on content, and I really need to provide a more detailed walkthrough. Please vote for this here if you would like this prioritized over the other changes in the queue.
“I noticed in the code that ssisUnit doesn’t handle password protected packages. When will this be supported?”
I’ve added an issue to track this here. Please vote for it if you use password protected packages, as it will help me prioritize how quickly to address it.
“If possible, can you demo if a container can be executed? Especially a For loop or For Each loop?”
I didn’t have time to demo this during the presentation. Good thing too, because there was an error around handling of containers. This has now been fixed in the source code version of the project.
“When testing in a 64bit environment, is there a specific way to execute ssisUnit when data sources are 32bit?”
Currently, this requires that you compile a 32bit version of the source. In the next release, I will provide both 32-bit and 64-bit versions of the test execution utility.
“Will the new version of ssisUnit for 2012 actually include some bug fixes? We’ve tried the current ssisUnit and it’s pretty buggy.” and “Not a question, just feedback: So there are over 30 bug reports and enhancement requests on CodePlex (including UI bugs, missing parts like handling complex Data Flow logic unit testing, etc.) posted since the posting date in 2008. If you address some of these (particularly the UI bugs) in a new release we might try it again. The biggest lack my organization found is that there’s no support for Data Flow tasks, which are 80% or more of our ETL testing. Just some feedback to keep in mind if you make any updates in the future. I’ll gather any additional bugs that we found in our evaluation back in 2011 and add them to the site when I have time.”
Again, sorry that you have had difficulties with it, and I’d definitely appreciate any feedback you can add to the site. For data flow testing, I do use that fairly successfully today, and a number of other users do as well. Admittedly, component level testing for the data flow would be nice (and it is being worked on) but I’m curious about what is blocking you from using it today. If you can submit your issues to the site, I will look at how to address them.
As far as the number of outstanding requests and UI bugs, well. unfortunately, I don’t get paid for working on open source. So focus on this project tends to take a backseat to demands from paying work. That being said, I do want to address as many of the issues as possible, but I have to prioritize my time pretty heavily these days. If there are items that particularly annoy you, please vote for them, as I do use the votes to determine what I should work on. For the UI, I really don’t enjoy UI work (nor, as evidenced by the current GUI, is it my strongest skill as a developer), so you are unlikely to see any significant updates on that front on the open source project. However, Pragmatic Works has taken an interest in unit testing as of late, and we are investigating offering an enhanced UI that’s integrated with Visual Studio as part of our BI xPress product.
“This makes sense to me, but I don’t understand how I setup a package to match up to the unit test.”
Each test case can reference tasks by name or GUID. GUID is more accurate, as names can conflict if you have multiple tasks with the same name in different containers.
“In package 1, you were getting a table count as a task and then writing a unit test to check the results of that test. Are you returning the result of the SELECT to a variable and then checking the value stored in the variable or are you directly testing the return value from the execute SQL task?”
The Execute SQL task in that test was designed to store the results of the SQL statement in a variable, so the test is written to check the value of the variable (using a VariableCommand) after the task executes. If you need to get a value directly from the database as part of the test, you can use a SqlCommand.
“Do unit tests always fall under Miscellaneous?”
Yes – the SSIS project structure doesn’t allow for custom folders.
“Would you have a more complex example for the test than the result of a table count?”
The ProductSample test on the website illustrates a few other test scenarios. If there are additional scenarios that you’d like to see examples for, please add an issue or discussion item on the site.
“Where I can get some xml to parse my .dtsx packages and only extract all the SQL code and associated SSIS task name?”
I’m not really sure how this relates, honestly. However, to do this, you’d need to either write a fairly complex XSLT transform, or use the SSIS API. Unfortunately, I don’t know of any public example code that illustrates this.
Many, many variations of “Can I get the slides?”
I mentioned this at the top of the post, but just in case: The recording of the webinar is available here: “Unit Tests for SSIS Packages”. You can download the slides from my SkyDrive, and ssisUnit can be downloaded from CodePlex.
Thanks again to everyone who attended, and thanks for all the great questions.
Here’s the scenario:
Within the PowerPivot Gallery in SharePoint 2010 you create a new “BI Semantic Model Connection”. In the “New BI Semantic Model” page you specify the name of the connection, the Workbook URL, and Database. When you click OK the following error is displayed along with a checkbox to “Save the link file without connection validation”. If you tick that checkbox and click OK then the BISM connection is created and works fine.
There were errors found while validating the page:
Cannot connect to the server or database.
The documentation from Microsoft does a really good job of explaining what is going on and what to do:
Here’s the text:
Grant Analysis Services Administrative Permissions to Shared Service Applications
Connections that originate from SharePoint to a tabular model database on an Analysis Services server are sometimes made by a shared service on behalf of the user requesting the data. The service making the request might be a PowerPivot service application, a Reporting Services service application, or a PerformancePoint service application. In order for the connection to succeed, the service must have administrative permissions on the Analysis Services server. In Analysis Services, only an administrator is allowed to make an impersonated connection on behalf of another user.
Administrative permissions are necessary when the connection is used under these conditions:
- When verifying the connection information during the configuration of a BI semantic model connection file.
- When starting a Power View report using a BI semantic model connection.
- When populating a PerformancePoint web part using a BI semantic model connection.
To ensure these behaviors perform as expected, grant to each service identity administrative permissions on the Analysis Services instance. Use the following instructions to grant the necessary permission.
Add service identities to the Server Administrator role
- In SQL Server Management Studio, connect to the Analysis Services instance.
- Right-click the server name and select Properties.
- Click Security, and then click Add. Enter the Windows user account that is used to run the service application.
You can use Central Administration to determine the identity. In the Security section, open the Configure service accounts to view which Windows account is associated with the service application pool used for each application, then follow the instructions provided in this topic to grant the account administrative permissions.
Go ahead and follow the directions to check Central Administration to determine the identify of the SharePoint service account, just make sure to select the correct application on the Credential Management page. In this case, “Service Application Pool – SharePoint Web Services System” should be the correct application.
Make note of the service account and it to the Analysis Services server admin group.
Also, make sure you’re adding the service account as a server admin, not a database admin.
If that doesn’t work it could be that you didn’t add the right service account. A good way to find out exactly what service account is being used, without having to fumble around with Central Administration, is to use the SQL Server Profiler. Start a new Profiler session on the Analysis Services server. While the Profiler is running, attempt to create another BISM connection. This is the result:
Look for the “Error” event class. The service account listed under NTUserName is the the account that needs to be added as a server admin for Analysis Services.
Here’s the scenario:
You have a PowerPivot Gallery within SharePoint 2010. Within the gallery is a BISM connection that points to a Tabular cube. You attempt to use the BISM connection to open a new Excel workbook by clicking on the Excel icon.
After Excel opens you get a Data Link Properties dialog box.
Trying to modify the Data Link Properties in any way is futile. Entering a value for “Location:” won’t do anything, Analysis Services only runs with Windows NT Integrated security (so entering a a specific username and password is useless), and clicking the down arrow to select the initial catalog will yield a Data Link Error…
The following system error occurred: The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail.
And once you click OK you get another lovely error…
Login failed. Catalog information cannot be retrieved.
Chances are you need to install the “Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2012″ from the which is located here:
Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2012
So you’ve got a new Sharepoint 2010 server configured and now you want to create a PerformancePoint dashboard. The first thing you need to do is create a new “Business Intelligence Center” site. You can find it under the Data category in the Create window.
Once the site is created you’ll see a page that looks like this:
Ok. Now we’re ready for business. In order to create a PerformancePoint dashboard you’ll need to open Dashboard Designer and there are several ways to do this.
If this is your very first time opening Dashboard Designer you’ll need to open it from the Business Intelligence Center. On the right hand side of the page you see three orange headings: Monitor Key Performance, Build and Share Reports, and Create Dashboards. Hover over the first heading, Monitor Key Performance, and click on the third link in the center of the page called “Start using PerformancePoint Services“. The next page that opens will have a large button called “Run Dashboard Designer”. Click it. You may be prompted to run an application, don’t be alarmed, this is actually the install for Dashboard Designer. It takes just a minute or so to complete and once finished Dashboard Designer will open. Of course it goes without saying that the install for Dashboard Designer only happens the very first time you click the “Run Dashboard Designer” button. After that, any time you click the button Dashboard Designer will open right up.
Now that Dashboard Designer has been installed you can open it from the Start menu. Click on All Programs, expand the SharePoint folder, and click on PerformancePoint Dashboard Designer. Make sure you expand the “SharePoint” folder and not the “Microsoft SharePoint 2010 Products” folder.
Once you have some PerformancePoint objects deployed to SharePoint you can open Dashboard Desiger from the PerformancePoint Content list. By default you can access this list from the left-hand nav bar, but if it’s not there you can get to it by clicking “All Site Content”. To open Dashboard Designer from the PerformancePoint Content list, click the down arrow for one of the items and select “Edit in Dashboard Designer”.
Now search for the OpenDD function name, it will probably be defined towards the top of the document.
Once it has been found, copy the function code and past into a Management Studio query window and transform it into working SQL code. You don’t have to do this, but I recommend it because the final expression is a bit tricky to do in your head. At least, it’s tricky for me to do in my head…
Here’s the actual SQL code…
DECLARE @designerRedirect AS VARCHAR(MAX)
SET @designerRedirect = '_layouts/ppswebparts/designerredirect.aspx'
DECLARE @siteCollection AS VARCHAR(MAX)
SET @siteCollection = '/sites/BI/'
DECLARE @siteLocation AS VARCHAR(MAX)
SET @siteLocation = '/sites/BI/Finance'
DECLARE @siteCollectionUrl AS VARCHAR(MAX)
SET @siteCollectionUrl = 'http://SQL2012Dev' + @siteCollection --= 'location.protocol + "//" + location.host' + @siteCollection
DECLARE @siteLocationUrl AS VARCHAR(MAX)
SET @siteLocationUrl = REPLACE(@siteLocation, @siteCollection, '') --=siteLocation.replace(siteCollection,"");
DECLARE @URL AS VARCHAR(MAX)
SET @URL = 'http://SQL2012Dev' + @siteLocation + @designerRedirect + '?SiteCollection=' + @siteCollectionUrl + '&SiteLocation=' + @siteLocationUrl
Make sure the values reflect your environment. When you run the query you should end up with something like this:
To test that it works, copy the URL into your browser and verify that Dashboard Designer opens with your content.
Apologies for the lack of updates to the blog recently. It’s been a very busy time, but hopefully things will settle down a bit now.
Exciting news today (for me at least)! It was my first day as a Pragmatic Works employee. I’ve joined their product group, and will be helping manage the development of their BI tools. As I’ve commented on this blog before, one of the things I ‘m really passionate about is enabling BI developers to create solutions faster and more easily, and I’m looking forward to the opportunities that Pragmatic Works presents to continue doing exactly that. I also get to work with a great group of developers and some really sharp BI people, so it promises to be a lot of fun.
My excitement is tempered somewhat by sadness at leaving another great group of developers at Varigence. I enjoyed working with everyone there, and wish them success in their future endeavors.
In other news, I have a number of presentations coming up. I’ll be at SQLBits in London on March the 29th, presenting a precon with Matt Masson on SSIS Performance Design Patterns (space is limited, register now!). I also have a session on SSIS Unit Testing at SQLBits.
Last, but definitely not least, I was thrilled to find out that I’ll be presenting the Tuning SSAS Processing Performance session at SQL Rally in Dallas on May 10-11 as well. Please vote for one of my other sessions in the community choice options, if you see one that appeals to you. I’m really looking forward to seeing some of my friends from Texas again.
Whether you’re creating a report, chart, KPI, or calculated measure, at some point you’ll probably need to add year-to-date calculations. It doesn’t matter if you’re using SQL or MDX, the technique to generate the From and To dates for a YTD calculation remains the same.
The general concept goes like this:
Step 1: Get the current year and prefix it with “01/01/”. This is the From date.
Step 2: Get today’s date. This is the To date.
It seems simple enough but if your ETL process runs daily and pulls “yesterday’s” data then you’ll need to account for a 1 day lag on the To date. For example, if today is June 15th, 2011 then your warehouse will only contain data up to June 14th. Using the logic described above, the YTD calculation will select data from “01/01/2011″ to “06/15/2011″, even though data for June 15th hasn’t yet been loaded into the warehouse. Although not entirely accurate, it won’t hurt anything, the YTD calculation just won’t return any data for the 15th.
Now let’s say today is December 31st, 2011. On this day the warehouse will only contain data up to December 30th and the YTD calculation will select data from “01/01/2011″ to “12/31/2011″. Are you beginning to see the problem? Here’s one last example to drive the point home: Lets advance one day to January 1st, 2012. On this day the warehouse will only contain data up to December 31st, 2011 but the YTD calculation will select data from “01/01/2012″ to “01/01/2012″. In this case the YTD calculation will return an empty resultset and the business users will never get a full 365-day view of the data.
Ok, so we know we have to update the YTD calculation to handle the one-day lag in the ETL. The logic now looks like this:
Step 1: Get the current year of yesterday and prefix it with “01/01/”. This is the From date.
Step 2: Get yesterday’s date. This is the To date.
To summarize, here’s what the YTD calculation will select using the original logic:
And here’s what the YTD calculation will select using the new logic:
As you can see, given the 1-day ETL lag, the new YTD calculation will always select the correct range of data, even on the first of the year.
But lets take it a step further. I propose the idea of working with the “last completed month” or even the “last completed week”. This way calculations are handled more gracefully. In other words, the YTD calculation is based on January through the last completed month or week. Keep in mind that we still have to account for the 1-day ETL lag.
This is what would happen if we decided to operate on the last completed month:
In order to achieve this behavior we must implement the following logic:
Step 1: Get the current year of yesterday and prefix it with “01/01/”. This is the From date.
Step 2: Get the month of yesterday’s date, append “/01/” for the day, append the year of yesterday’s date, and subtract one day. This is the To date.
The benefit of this approach is that business users have up to a month to analyze data produced by year-to-date calculations. However, it’s a double edged sword because business users have to wait up to a month. Admittedly this approach may be a better choice for “rolling” calculations but this will depend on what works best for the business. In some cases using the last completed week may be preferred over using the last completed month since users still have time to analyze data but don’t have to wait long for new data to be included in the calculation.
This topic will cover how to export data from BLOB fields in SQL Server and save those files out to the filesystem. This is accomplished within VB.Net code, but could just as easily be ported over to C#.
DBServer: SQL Server 2008 SP2
Development Environment: Visual Studio 2008
Public Sub SaveMemoryStream(ByVal buffer2 As Array, ByVal FileName As String, ByVal ms As MemoryStream) Dim outStream As FileStream outStream = File.OpenWrite(FileName) outStream.Write(buffer2, 0, ms.Position) outStream.Flush() outStream.Close() End Sub Public Sub Main() Dim Folder_Path As String Dim File_Name As String Dim s_File_Name As String Dim buffer() As Byte Dim oStream As System.IO.MemoryStream = Nothing Dim oFileStream As System.IO.FileStream = Nothing Dim buffer2() As Byte Folder_Path = Dts.Variables("Working_Directory").Value.ToString() + "" File_Name = Dts.Variables("File_Path").Value.ToString() s_File_Name = Folder_Path & File_Name Try buffer = CType(Dts.Variables("Blob").Value, Byte()) oStream = New System.IO.MemoryStream(buffer) oStream.Write(buffer, 0, buffer.Length) buffer2 = oStream.ToArray() SaveMemoryStream(buffer2, s_File_Name, oStream) 'Close the stream oStream.Flush() Dts.TaskResult = ScriptResults.Success Catch ex As Exception Dts.Events.FireError(0, "Create Temp Image", ex.Message, "", 0) Dts.TaskResult = ScriptResults.Failure Finally If Not oStream Is Nothing Then oStream.Close() End If oStream = Nothing If Not oFileStream Is Nothing Then oFileStream.Close() End If oFileStream = Nothing End Try ' Dts.TaskResult = ScriptResults.Success End Sub
I briefly mentioned a technique I used to deploy custom assemblies for SSRS to the GAC in my PASS presentation on
Building a Reporting Services Framework. I didn’t spend a lot of time on that, but I did get some questions on it, so I thought I’d go into more detail here. Here’s a pointer to the documentation around custom assembly deployment from MS. This post is how to automate the process if you’re using the GAC method.
Here is a code sample showing how I perform the remote deployment.
Before we actually get around to deploying to the server we need to be able to test our custom assembly locally. I use the following Post-Build Event in my custom assembly. If you aren’t familiar with Build Events, you get to them by right-clicking on the project and choosing ‘Build Events’ in the left hand side of the properties.
copy "$(TargetPath)" "%ProgramFiles%Microsoft Visual Studio 9.0Common7IDEPrivateAssemblies" gacutil.exe -i $(TargetPath) copy /Y "$(TargetPath)" "C:_PASS_DemoDeployAssemblies"
This build event does three things:
Here is the solution that I found for automating deployments for other servers. You have to jump through a few hoops to be able to do this (at least with Windows Server 2008 R2). If you search the web you’ll find a few other solutions to this problem, but I didn’t find any that met my specific needs. The specific scenario I was trying to enable was unattended deployments. If you log in to the machine, or if you’re able to provide credentials, this is an easier problem. This was the only way I found to do this without manually collecting credentials. If you do that and use credssp, you can use PowerShell to do this remotely. If you are willing to send an admin password clear text, you can use PSExec. Neither of those two solutions met my needs, so I chose the following approach:
You might need to tweak the example scripts a little bit for your environment. You might also want to dot source the DeploymentLibrary script (i.e., ./DeploymentLibrary.ps1) in deploy_ssrs_assemblies.ps1. I have all my libraries load as part of my profile, so this may assume the library is already loaded. The installation process just takes the path you deployed the assembly to (such as “\Server01-devd$ReportsdeploymentassembliesEltUtilities.dll”) and figures out the name of the assembly and the server from it.
The sample I provided is laid out as follows:
There are a few moving pieces to this process, but the example should be pretty well documented.
That’s about it. If this solution doesn’t meet your specific needs, there are a few other ones out there (such as Remote GAC Manager) that might, or you can just use the standard PSExec approach. Happy automating!
I presented my session on ‘Building a Reporting Services Framework’ at the PASS Summit 2011 on Friday. I had a great time at the summit, both presenting and attending all the great sessions. A wonderful time was had by all.
Here’s a link to the demo materials I went through. You probably don’t want to just push any of this out to production as is, but it should be a good start. I also used the Adventure Works 2008 R2 database, but it isn’t included in this package.