Going to the PASS Business Analytics Conference

I found out recently that I’ll be able to attend the PASS Business Analytics Conference this year, which I’m pretty excited about. Also, I’m not presenting at this conference, so I will actually get to relax and enjoy the sessions by other speakers. If you haven’t registered yet, now’s a good time*.

There’s a lot of great content at this conference, and it’s a bit challenging in some time slots to decide on exactly what I want to see most. However, there are 3 sessions that I will definitely be attending:

AV-400-M – Deep Dive into Power Query Formula Language

Power Query is a cool technology for data transformation – one that I believe (hope) will continue to evolve and become relevant outside of just Excel. And it’s usefulness in quicking mashing up some data inside Excel is outstanding. This is a focused session on the formula language, which I’m interested in, and it’s being delivered by Matt Masson and Theresa Palmer-Boroski. Matt does a great job on these types of presentations. I haven’t seen Theresa present yet, but I’m confident she’ll do a great job, and this will be a good session.

ID-100 – Creating an End-To-End Power View Reporting Solution

Devin Knight (a co-worker at Pragmatic Works) is delivering this, and he puts together great sessions. Power View is one of those technologies that I don’t spend a lot of time with, but I know I need to know it better, and this session should help with that. Devin has a lot of practical experience with Power View, so this will be a great opportunity to get a real world look at what’s involved.

SA-102 – Analytics and OLTP Systems: Is Hekaton A Game-Changer?

Hekaton is the new in-memory technology in 2014. It’s primary focus is on improving the performance of OLTP applications, but Todd McDermid will be looking at it from the perspective of delivering analytics. He’ll be answering the question of whether it can be used to deliver a single database that suited for both transactional processing and analytics, and I’m very interested to see the results. I feel like the Hekaton technologies could have a place in the BI world, but I haven’t had a chance to go out and really investigate it myself. Thanks to Todd’s efforts, I won’t have to.

There are a lot of great sessions, and those are just 3 of the ones that appealed to me.  I’m really looking forward to attending, and I hope to see you there.

*If you aren’t already registered, you can use the discount code BABQ9B to get $150 off your registration.

Posted in Uncategorized | Comments Off

Demo Materials for PASS Session BIA-302 – Building a Supportable ETL Framework

Last week I presented the session “” at the PASS 2013 Summit.

Here’s a link to the demo that I went through.

BIA-301 – Demo Materials

Posted in Uncategorized | Comments Off

Q & A From “Unit Tests for SSIS Packages”

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.

Testing Practices

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

Automating ssisUnit in Builds

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

Compatibility

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

Setting Up and Using Tests

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

Miscellaneous

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

Sharing the Presentation

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.

Posted in Presentations, SSIS | Tagged , | Comments Off

Cannot Create a BI Semantic Model Connection to Tabular Cube

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:

http://msdn.microsoft.com/en-us/library/hh230972.aspx#bkmk_ssas

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

  1. In SQL Server Management Studio, connect to the Analysis Services instance.
  2. Right-click the server name and select Properties.
  3. 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.

 

Posted in Uncategorized | Comments Off

BISM Connection to Tabular Cube and Data Link Properties

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:

http://www.microsoft.com/en-us/download/details.aspx?id=29065

Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2012

      The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2012 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Posted in Uncategorized | Comments Off

How to Open Dashboard Designer in SharePoint 2010

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.

Method #1

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.

Method #2

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. 

Method #3

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

Method #4

The final, and in my opinion, best way to open Dashboard Designer is by using a URL.  Not a URL to the “Run Dashboard Designer” page from Method #1, but a URL that opens Dashboard Designer directly.  To do this you’re going to have to do a little sleuthing.  First thing to do is navigate to the “Run Dashboard Designer” page from Method #1.  Assuming you’re using Internet Explorer, make sure the Menu bar is displayed in your browser.  Click on View and select “Source”.  This will open up the HTML source code behind the webpage.  Since we know that clicking the button automagically opens Dashboard Designer, do a search in the HTML source code for the text on the button, “Run Dashboard Designer”.  Notice the OnClick method of the button is calling a Javascript function called “OpenDD”. 

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

SELECT @URL

Make sure the values reflect your environment.  When you run the query you should end up with something like this:

http://SQL2012Dev/sites/BI/Finance/_layouts/ppswebparts/designerredirect.aspx?SiteCollection=http://SQL2012Dev/sites/BI/&SiteLocation=Finance/

To test that it works, copy the URL into your browser and verify that Dashboard Designer opens with your content.

Posted in PPS, SharePoint | Comments Off

Where’s John These Days?

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.

On April 14th, I’ll be presenting at SQL Saturday #111 in Atlanta, which is always a great time. I’ll be presenting on Tuning SSAS Processing Performance

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.

Posted in Events | Tagged , , | Comments Off

YTD Calculations

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:

  • June 15th, 2011: “01/01/2011″ through “06/15/2011″
  • December 31st, 2011: “01/01/2011″ through “12/31/2011″
  • January 1st, 2012: “01/01/2012″ through “01/01/2012″

And here’s what the YTD calculation will select using the new logic:

  • June 15th, 2011: “01/01/2011″ through “06/14/2011″
  • December 31st, 2011: “01/01/2011″ through “12/30/2011″
  • January 1st, 2012: “01/01/2011″ through “12/31/2011″

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:

  • June 15th, 2011: “01/01/2011″ through “05/31/2011″
  • December 31st, 2011: “01/01/2011″ through “11/30/2011″
  • January 1st, 2012: “01/01/2011″ through “12/31/2011″

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.

Posted in MDX, SQL | Comments Off

How to Export BLOB data to Files in SSIS

Overview

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

Package Setup

  1. Add an Execute SQL Task that retrieves the BLOB information from the Database
    • ResultSet: Full result set
    • Parameter Mapping: Add any necessary parameters for your stored procedure
    • Result Set
      • Result Name: 0 (zero)
      • Variable Name: Doesn’t matter, so long as the variable is of type System.Object
  2. Connect the Execute SQL Task to a For Each Loop Container (FELC)
    1. Collection: Foreach ADO Enumerator
    2. ADO object source variable: Result Set Variable Name from Step 1
    3. Enumeration mode: Rows in the first table
    4. Variable Mapping: Map out the columns from the Result Set to Package Variables (Index is 0-based)
      1. NOTE: Be sure to setup the BLOB output variable as a System.Object
  3. Add a Script Task to output the BLOB information to the file system

Script Code (VB.Net)


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
Posted in Uncategorized | Comments Off

Deploying a custom SSRS Assembly to the GAC

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.

Local Deployments

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:

  1. Copies the dll to the PrivateAssemblies directory.  This makes the custom assembly available to BIDS so you can use it in Preview Mode.  You have to restart BIDS after updating the custom assembly for the new version to be loaded.
  2. GAC’s the custom assembly so it can be used by your local Reporting Services instance.  This will require a restart of the service to be live, but I typically don’t include that in the build process because it takes a few seconds and I’m use it less often.
  3. Copies the dll to the Deployment section of my solution.  I typically source control the dll, and use the process described later in this post to deploy the assembly to other environments.

Remote Deployments

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:

  1. Copy the assembly to the target machine.
  2. Install the assembly using System.EnterpriseServices executed via a remote call.
  3. Verify the assembly was actually installed (the installation procedure doesn’t return failures).
  4. Restart the ReportServer service.

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:

  1. Deployment – The root of the deployment structure.
    1. Assemblies – The assemblies we want to deploy.  I also typically include other libraries used for other types of automated deployments here as well.
    2. Dev – This contains a simple driver file that sets all the variables necessary for a deployment to the dev environment.  I usually also have a localhost, qa, and prod folder as well (identical scripts except for the variables).  The code to set up the logging should probably be refactored into another method in the PowerShell library, but that’s on the TODO list.
    3. deploy_ssrs_assemblies.ps1 – The script that handles the orchestration of the deployment.  This is common, parameter driven functionality that is called by the drivers for each of the environments.
    4. DeploymentLibrary.ps1 – The methods that install the assembly on the remote server and restart the services.

There are a few moving pieces to this process, but the example should be pretty well documented. 

Conclusion

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!

Posted in How To | Tagged | Comments Off