Jessica M. Moss

While looking in SQL Server Management Studio for something else entirely, I stumbled upon the Template Explorer.  This window provides Analysis Services templates for querying data mining structures (DMX), querying cubes (MDX), and performing DDL (XMLA).  I took a deeper look into the “MDX Queries” templates. To view the Template Explorer, select the View menu > Template Explorer option.  To see the Analysis Services template, select the Analysis Services cube option at the top of the window.  Double-clicking any of the templates listed will then open a new query window containing the selected query.  For example, the Basic Query will show the following: Select     <row_axis, mdx_set,> on Columns ,         <column_axis, mdx_set,> on Rows From     <from_clause, mdx_name,> Where     <where_clause, mdx_set,> You have two choices at this...

When designing a SQL Server Integration Services package, it can seem tedious to drag over each and every task and component from the toolbox to your Control Flow and Data Flow and connect all of the precedence constraints and pipelines. You can alleviate some of this by modifying the default Business Intelligence options within Business Intelligence Development Studio (BIDS). Under the default settings, you can double-click any toolbox item and it will show up in your package designer with no connectors or specific place. To improve this, open up the Tools > Options... menu in BIDS. Then expand Business Intelligence Designers and Integration Services Designers. You will see Control Flow Auto Connect and Data Flow Auto Connect. If you check the option to "Connect a new shape to the selected shape by default", the drop down lists for specifying connector type and location are enabled in each menu. I prefer to use a Success constraint and add the new shape to the right of th...

Over the past two months, I had the honor of presenting at many user groups and conferences. I wanted to put together some information for those who attended (and for those who were unable to attend!). If you saw either "Building Reports in SQL Server Reporting Services 2008" or "New Features of SQL Server Integration Services 2008" and are excited to get your hands on a SQL Server 2008 instance, but your company won't upgrade... you can download a free trial/evaluation version from Microsoft. Maybe you can even show your boss some of the things you learned from my presentation! @AndyLeonard: “You got your SSIS in my Twitter!” @JessicaMMoss: “You got your Twitter in my SSIS!” [Previous dialogue shamelessly stolen from Brent Ozar's blog because it's a perfect lead-in to...] For those of you who are not familiar with Twitter , it's a micro-blogging tool / social network platform that has completely taken off over the past year. Andy Leonard invited ...

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