Jessica M. Moss

I'll be flying up and down the east coast over the next two weeks, sharing information about the SQL Server Business Intelligence suite. I do hope you can make it to one of the sessions! 11/3 - Tampa Bay SQL Server Business Intelligence User Group - SSRS 2008 11/6 - Sarasota SQL Server Users Group - SSIS 2008 11/11 - Orlando PASS - SSRS 2008 11/13 - Richmond SQL Server Users Group - SSRS 2008 11/15 - Raleigh Code Camp - SSRS 2008

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

Using the default IIS 7.0 installation on Windows Server 2008 does not include all of the role services necessary to install SQL Server Reporting Services 2005. I found this command in an online forum when looking for a list of all the needed role services. (By the way, I apologize, but I can no longer find the post to link back to. If it was you who wrote it, please post a comment with a link back to that post!) Here is the command. I hope it helps you as much as it helped me! ServerManagerCmd.exe -i Web-Server Web-Asp-Net Web-Http-Redirect Web-Windows-Auth Web-Metabase Web-WMI Version: SQL Server 2005

On October 1, 2008, I received notification that I was awarded as a Microsoft Most Valuable Professional . According to Microsoft, MVPs are "exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high quality, real world expertise in offline and online technical communities" and quite a few other comments that make me blush. It's always nice to be rewarded for doing what you love to do. MVPs are awarded for their past contributions, but the benefits are applied to the upcoming year. These benefits include access to technical resources and the opportunity to learn from and offer thoughts to Microsoft. I believe the information I provide will be best if it comes from everyone, so I ask you to let me know if you're having any issues or concerns with SQL Server. On a final note, if you have a user group that would enjoy learning about SQL Server BI, or if you have a technical question, or if you just feel like geek...

That would be Richmond in Virginia and Olympia in Washington. :) I'll be speaking at the Richmond Code Camp 2008.2 on October 4 about custom code in Reporting Services. Richmond is my local user community, and I can't say enough good things about the crew that organizes and speaks in that area. I hope to see some familiar faces during the day. The following Saturday (October 11), I'll be presenting at Olympia SQLSaturday about Reporting Services 2008. I enjoy SQLSaturday events because they are all about SQL Server! This one includes talks on Business Intelligence, TSQL, and internals. Both events have a great speaker lineup and should be a lot of fun. Hope to see you there!

When someone is querying a new Analysis Services cube, there is one mistake that I often see made: trying to use a dimension that does not relate to the desired measure group. For example, when looking at the Adventure Works cube, if you try to use any of the Reseller measures with the Customer dimension, you will receive the same value across the Customer members. Because there is no relationship defined in the cube, the MDX query will use the default member for that particular attribute, which, in this case, is the ‘All’ member. It would look something like this: This type of situation typically goes away once a user has learned the new data model, but Management Studio 2008 provides a neat dropdown list to help initially avoid the rogue query. On the Metadata tab, under the label Measure Group, you can select the measure group you are targeting. The GUI refreshes to only show the dimensions and measures that are associated with that measure group. If we select the ‘Reseller Sales’ ...

This will be my first year attending the PASS Summit as a speaker rather than an attendee. I will be joining Rushabh Mehta to present a spotlight session entitled "Building an SSIS Management Framework". The PASS Summit is a great conference that focuses on SQL Server and Business Intelligence. Because it is in Seattle, there will be a ton of people from the SQL Server Developer Team and SQLCAT. If you have any questions about SQL Server, this is the place to be. You still have a few weeks left to sign up. Solid Quality Mentors is also offering a discount code applicable to the cost of the conference. You can get that code from here: http://www.solidq.com/na/pass.aspx . So now you have no excuse left to not sign up!

In a previous post , I showed how to dynamically limit the number of rows shown in a table or matrix. This methodology used a filter on the table to only show the desired number of rows. The disadvantage with this method is that all rows are returned, even if only a small subset of rows is actually desired. To alleviate this problem, you can use a query parameter to restrict the number of rows returned at the query level. This does introduce a different problem. Every time the report is run with a different Top N parameter, the query will be rerun to bring back the correct number of rows. You will need to determine which method is optimal for your situation. Here are the steps to create a sample report to show this new method. 1. Add a report parameter named “NumberPpl” of data type integer. 2. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT TOP(cast(@TopN as int)) DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResell...

I have an exciting week coming up! On Wednesday evening, I will be speaking at the Rockville .NET User Group on SSIS Tips & Tricks for both SQL Server 2005 and 2008. If you're in the area, I hope you'll stop by. On Friday night, the Northern Virginia Girl Geek Dinner is having their inaugural dinner. Hosting by Viget Labs , sponsorship by Microsoft , and a presentation by Dr. Joanne McGrath Cohoon will make for a great evening! I will be there, helping to ensure everything runs smoothly. The sign up deadline was just extended through Wednesday, so sign up now at http://novagirlgeekdinner.eventbrite.com/ !

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

In SQL Server Reporting Services, you can dynamically limit the number of rows that are displayed in a table or a matrix. Here are the steps to create a sample report showing this. 1. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount FROM DimEmployee INNER JOIN FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey WHERE (FactResellerSales.OrderDateKey = 20011001) GROUP BY DimEmployee.FirstName, DimEmployee.LastName 2. Add a report parameter named “NumberPpl” of data type integer. 3. Add a table to the body of the report with the data fields of firstName, lastName, and salesAmount. Sort by the column salesAmount from ‘Z to A’. 4. Set the Filters option of the table as shown below. (This is where the real work is happening!) 5. Preview the report, modifying the value for the parameter NumberPpl to see the number...

Those of you who visit my blog directly may notice a new image labeled "WorldMaps" on my right-hand side bar. WorldMaps , introduced to me by Andrew Duthie , stores statistics about visitors to any website. By signing up for an account and adding the tracking device on your page, you can keep track of the number of hits, different IP addresses, visitor locations, and more. For an example of what WorldMaps can provide, click on my world image, which will link to a page with my statistics. For other statistics of interest, I also use Feedburner . While I'm happy with the information they've provided, there is something about seeing my visitors’ locations visually that speaks to my Business Intelligence side ;) I definitely recommend you take a look at WorldMaps!

After converting your SQL Server Reporting Services reports from 2005 to 2008, you may notice a difference in the way the report looks. One possible reason for this difference is the change in the way overlapping report items are rendered. In both SSRS versions, the message in the Output window is similar to: "[rsOverlappingReportItems] The text box ‘Textbox2’ and the text box ‘Textbox1’ overlap. Overlapping report items are not supported in all renderers." In Reporting Services 2005, these textboxes would display in an overlapped fashion, often causing the text within both textboxes to be unreadable. In Reporting Services 2008, however, the second textbox shifts, so that it appears the textboxes are vertically next to each other. This allows you to always read the text in both textboxes. The new overlap rendering functionality is included in the "Soft Page-Break Renderers", including the Report Preview window, HTML, MHTML, Word, and Excel. Taking a look at the...

To start working with the management side of SQL Server Reporting Services 2008, I decided to set up a report server and report manager. Unfortunately, I made a mistake while setting up my configuration that left me a little perplexed. Here are the steps I took to cause, track down, and solve the issue. Problem: I began by opening the Reporting Services Configuration Manager from the Start Menu. I clicked through each of the menu options and accepted the defaults for any question with a warning symbol, since warning symbol typically designate an action item. After two minutes, all of the warning symbols had disappeared, and I was ready to begin managing my report server. Unfortunately, opening up a browser and trying to open up the report manager resulted in the dreaded " The report server has encountered a configuration error. (rsServerConfigurationError) " message. Sherlock-ing it: I put on my sleuthing hat and went to the log file directory: C:\Program Files\Microsoft...

I’ve recently had the pleasure of diving into Reporting Services 2008 RC0, and I’m amazed at the difference in the design environment from SSRS 2000/2005! To give a comparison of the designer, I took a look at the BIDS Reporting Services project. Overall, I think this version is more slick and more user-friendly (once I find everything again :) ) than its predecessor. To begin, there is no longer a Data tab in the main frame of the design environment. You can find your DataSets and Fields on the “Report Data” window on the left side of your designer. If you lose your Report Data window, click the View menu > Report Data, or just select Ctrl-Alt-D. Also joining the new Report Data family are Report Parameters, originally found on the Report menu, and Fields, originally on its own window. A look at the new RS would be remiss without mentioning the new Tablix control. From the toolbox, you still select a Table, Matrix, or List control, but these are templates for the Tablix contr...

I've been tagged with the Software Development meme, courtesy of Andy Leonard . And, we're off! How old were you when you first started programming? I was 13 years old in the eighth grade. I took an Introduction to Programming class with an amazing teacher. Thanks to Mr. Creasy, I've been hooked ever since. How did you get started in programming? My father was a computer programmer, so I guess I come by it naturally. I used to help my teachers work their classroom computers in elementary school, and then tried my hand at programming in middle school. What was your first language? BASIC. Not VB.Net. Not Visual Basic. Just BASIC. How many Millennials can say that? ;) What was the first real program you wrote? Hello world... Or something along those lines that was pretty simple. What languages have you used since you started programming? BASIC, C++, Java, C#, VB.NET, ASP.NET, T-SQL, MDX... others I'm sure... What was your first professional programming gig? My fir...

I just finished the book The Elements of Mentoring by W. Brad Johnson and Charles R. Ridley at the suggestion of Andy Warren in his blog series on mentoring . While I’ve been fortunate enough in my career to have many people have advised, taught, and coached me, I’ve never been involved in a formal mentor-protégé relationship. After reading all of the work involved, I’m surprised anyone would want to! That statement is partly tongue-in-cheek because it is apparent after reading this book that a successful pairing can be mutually beneficial. Here are just a few thoughts on the book. The Elements of Mentoring starts by discussing how to be a good mentor, namely that it is important to have both the right personality and the right skill set. It struck me as I read that for most of the advice, I could replace mentor/protégé with any relationship pair, i.e. parent/child, person/spouse, etc. For example, the important of bolstering the protégé’s confidence is a pillar of good parenti...

I'm excited to be making the trip up to Vancouver on June 26 to speak at the Vancouver PASS User Group on the new features of SSIS 2008. Also speaking will be Smruti Parab on SQL Server 2008 SSAS and Scott Stauffer on the new features of SSRS 2008. In my presentation, we'll take a look at the data profiler task, the new lookup transformation functionality, and whatever else strikes me before that time. :) If you're in the area, I hope to see you there!

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

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

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

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

I am, with GeekyCyberMom , putting together a Girl Geek Dinner group for the MD/VA/DC area. Girl Geek Dinners started in London by Sarah Blow and have cropped up all over the world. They are a way for women to get together in a welcoming environment to learn and discuss technology. We are looking for sponsors and members. If you are interested in helping with or attending any of the events, catch up with us through here: Blog Wiki Twitter I know I'm looking forward to it!

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

After recovering from a serious lack of sleep from presenting at SQLTeach :), I wanted to jot down a couple of thoughts about the conference. 1) Jean-Rene Roy puts on an amazing conference. The DevTeach conference has been around since 2003, and this was the first year it was in Toronto. 2) The quality and experience of the speakers is of the highest quality, and since the conference is fairly small, attendees get a great experience with often individualized advice and attention. 3) I despise scaled evaluations. The scale used at this conference was as such: bad, average, good, excellent. While my presentations were rated fairly well, the problem is that this system promotes subjectivity under the guide of objectivity. Who is to say what a "good" versus an "excellent" presentation is? Are you a glass half empty or a glass half full type of person? 4) The conference contained a whole Agile track. I was fortunate enough to spend some time with these speakers le...

I'll be speaking at the NoVa CodeCamp 2008.01 in Reston, VA tomorrow, May 17. The presentation will be "SSIS Tips & Tricks" - an advanced look at SQL Server Integration Services with a focus on increasing developer productivity. I'm looking forward to the experience and meeting more of the developers in the area. If that's you, you should stop by!

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

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

I'm thrilled to be speaking at the SQLTeach Toronto conference in a few weeks. I will be presenting two topics: "SSIS Tips & Tricks" and "Migrating DTS Packages to SSIS". If you're going to be in the Toronto area from May 12 - 16, come check it out! A quick preview of what you can expect at the SQLTeach/DevTeach conference: Silverlight 2.0 post conference workshop Party with Palermo Scott Hanselman keynote An amazing line-up of speakers on .Net, SQL, and development artchitectures I hope to see you there!

It's not surprising to anyone working in technology that we work in a male-dominated field. If you've been to a technical conference recently, it's also not surprising that the percentage of female speakers is drastically lower than male speakers. Is this due to the obvious gender imbalance in the field, the difficulty in finding women to speak, or something else? I'm not going to try to venture a guess - I'm just glad to embrace a site that is trying to "even things up a little" by providing a repository of women technical speaker bios for conference organizers to utilize. GeekSpeakr: http://www.geekspeakr.com Disclaimer: You'll see my bio on there too :)

After migrating a package from DTS to SSIS, I had a problem with an Execute SQL Task. I couldn't change any characters in the SQLStatement property; I couldn't add any new characters; I could delete characters, but not retype them! After googling several variations of "integration services" "read only" and "Execute SQL Task", I deleted about half of the entry in a fit of frustration. Lo and behold, I could type again. Apparently, there is limit on the size or number of characters that can be entered in the SQLStatement property. From my experimentation, I came up with a limit of 32767 characters. The interesting thing is that the restriction only seems to be on the designer. If you set the SourceType to "Variable" and use a variable that contains more than 32767 characters, the task will execute. Also, if you use the "Direct Input" SourceType and modify the package XML to set the SQLStatement longer than 32767 characters, ...

Trying to find an SSIS package in SQL Server Management Studio is difficult because the packages are ordered by the date they were imported. You can make a quick adjustment to a system stored procedure, which will show the packages in alphabetical order. Modify the [msdb].[dbo].[sp_dts_listpackages] stored procedure to include an order by at the end: ALTER PROCEDURE [dbo].[sp_dts_listpackages] @folderid uniqueidentifier AS SELECT name, id, description, createdate, folderid, datalength (packagedata), vermajor, verminor, verbuild, vercomments, verid FROM sysdtspackages90 WHERE [folderid] = @folderid ORDER BY name and Voila! your packages will be listed in alphabetical order when you view them in SSMS. As of SQL Server 2008 CTP6, this functionality has already been added. The new stored procedure that is executed when expanding the list of packages in SSMS is named: [msdb].[dbo].[sp_ssis_listpackages]. Versions: SQL Serv...

SQL Server Integration Services contains the SSIS log provider for SQL Server, which can be used to log records to a SQL Server database in the sysdtslog90 table. All sorts of events and information can be recorded during the execution of a package. If you were to turn on logging, but turn off all possible events to be logged, you would still end up with two events being fired and recorded: PackageStart and PackageEnd. These are also the events that are normally used when reporting on how the packages performed. See http://www.microsoft.com/downloads/details.aspx?FamilyId=D81722CE-408C-4FB6-A429-2A7ECD62F674&displaylang=en (SSISEventLogReportPack.exe) for examples of reporting on execution times, success/failure counts, etc. The bite comes when you have a package that either fails very early in its validation phase, or if you stop the package before it's really started to rev up. If you include all events in your logging, both an OnPreValidate and an OnPostValidate event ...

The DTS Dynamic Property Task does not have a one-to-one migration path to SSIS. To emulate the same functionality, you can use package configurations, variables, expressions, or decide to get rid of the dynamic property piece entirely. Which of these options you choose is completely dependent on your scenario, but I’ve put together a quick and dirty mapping from the type of dynamic property to the new SSIS functionality. DTS Dynamic Property Task Type SSIS Functionality INI File Package Configuration Query Execute SQL Task, Variable, Expression Global Variable Expression Environment Variable Package Configuration Constant Expression Data File Package Configuration Good luck in your migration! Version: SQL Server 2000 SP4, SQL Server 2005 SP2

In DTS, message boxes were a great way to debug packages and especially ActiveX scripts. In SSIS, other methods of tracking down issues, such as breakpoints and logging, are the way to go. In fact, message boxes aren't allowed in SSIS unless you are running the packages on a client machine. If you run a package on a server with the following code: MsgBox( "This is a message box!" ) You'll see an error message similar to this: Error: 2008-03-18 23:00:03.30 Code: 0x00000002 Source: Script Task Description: The script threw an exception: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:02 PM Finished: 11:00:03 PM Elapsed: 0.546 seconds. The package execution failed. The step failed. If you come across that ...

Every blog needs to have a welcome post, so welcome! My name is Jessica Moss (could you tell from the title of the blog? ;) ), and I'm a Microsoft SQL Server Business Intelligence Mentor with Solid Quality Mentors . I specialize in Integration Services but work with the entire BI suite of products. I'll mostly be posting about cool things I find in my BI adventures, tips and tricks, and anything technical that piques my interest. I would love to hear from you in the form of comments and/or questions. Enjoy!