Jessica M. Moss

Do you have a neat T-SQL trick that you'd like to show off? Is your idea of a good time figuring out Analysis Services hierarchies? Have you solved world peace using SQL Server? If so, you should submit an abstract to speak at SQLSaturday Richmond! We are currently accepting speaker submissions. You can submit an abstract by going to the website: http://www.sqlsaturday.com/30/callforspeakers.aspx ! Please submit by 12/12/2009. If you're not interested in wowing people with your extensive SQL ninja skills, we hope you will consider attending this amazing SQLSaturday event first time ever in Richmond, VA. Obligatory marketing blurb with further information below: Join the Richmond developer community for a free, one-day training for SQL Server professionals, developers, DBAs, and even people who don't know how to spell SQL. When: January 30, 2010 Where: ECPI College of Technology 4305 Cox Rd Glen Allen, VA Admittance to this event is free; all costs are covered by donation...

To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind.  The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) )  Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment. Components It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers.  The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server.  This is a free download that can be found here (2005) or here (2008) . Security Once you have installed all components, you must complete a series of screens in the SharePo...

One of my favorite times of the year is the PASS Community Summit.  It's five jam-packed days of SQL Server sessions by Microsoft employees, MVPs, and the people that you read about in technical books.  It's pre-conference and post-conference workshops on all different levels of topics of SQL Server.  It's a way to meet new people who love to do what you love to do and connect with old friends who you only see once a year.  It's a way to get a job or sell a job.  It's all of this and so much more that I can't even begin to describe. I'm excited to attend for the third year starting tomorrow.  If you are attending the Summit, I hope you'll come and introduce yourself.  You can find me at one of these locations/times: Monday, November 2, 2009 4:30pm-6:30pm   Networking Pre-Con - I'm really excited to learn about the best ways to meet people and meet other people who are interested in the same thing! Tuesday, November 3, 2009 11:45am...

As promised, I wanted to let you know about a few events where I'll be in the upcoming weeks. This Saturday (10/17), I'm making the trek up to East Iowa for their SQLSaturday event. They pulled in quite a few great speakers, and they've also invited me to speak on my favorite BI products: SSRS, SSIS, and SSAS! It looks like registration is still open, so if you're in the area, you should definitely sign up, come by, and say hi. The following week, I am pleased to be speaking at the Charlotte SQL Server User Group on Extract, Transform, and Load your Data Warehouse . I'll be making the drive down (audio book suggestions, anyone?) on Thursday, October 22 to speak that evening. I'm looking forward to meeting new people and seeing some old friends in the area. I'll also be staying over for a soon-to-be-announced event on Friday. The first week of November, I'll be at the PASS Community Summit . These is my third year attending the conference, second...

Over the years, I've read several posts similar to this one, and now it's my turn!  At the beginning of this year, I was asked to participate in writing a new SSIS book with co-authors: Erik Veerman, Brian Knight, and Jay Hackney.  After many late nights and just a few cans of Mountain Dew, I'm proud to say that the book has been completed and will be published at the beginning of November.  Please check out: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution . Along with that exciting news comes an apology for not blogging as much as I would have liked.  Here's a quick recap from the past several months: David Giard invited me to do a short video on Integrations Services on his Technology and friends series. I had a great conversation with Richard Campbell and Greg Hughes on Reporting Services on RunAs Radio . The PASS BI Virtual Chapter had me speak a few times.  I definitely recommend you check out the line up of great spe...

The SQLSaturday phenomenon is taking over the nation!  I was honored to be selected to give a presentation at the Pensacola event on June 6, 2009 on PerformancePoint Server 2007 M&A (I'm still waiting to hear more information about next version incorporated with SharePoint :))  I had a great time seeing the SQLSaturday crew, old friends, and meeting some new friends! While I was down in beautiful Pensacola, I was also thrilled to participate in the third episode of the It's All About The Tools TV Show !  I gave a demonstration on the SSIS 2008 data profiling tools - you can check it out here: http://channel9.msdn.com/shows/toolshed/Show-Episode-3-Its-All-About-The-Tools-TV-Show/ .  Russ Fustino and Stan Schultes put on a great show that I'm sure you're going to enjoy! I really enjoy speaking at events and sharing information on data warehousing and the Microsoft business intelligence suite.  I’ll warn you in advance that I prioritize engagements based...

On June 8-12, I'll be attending and speaking for the DevTeach/SQLTeach Vancouver conference . I've written about SQLTeach before, and I have to say that it's one of my favorite conferences. It's a small group of attendees with speakers who are some of the biggest names in the field. You really get a great opportunity to learn from and hobnob with speakers such as Peter DeBetta, Bill Graziano, Kevin Kline, Joe Webb, and more! I know I'm looking forward to picking their brains on a few things. A few highlights of the conference: Monday night: Party with Vancouver IT community DevTeach Kickoff party Weekend after: Alt.NET event - DevTeach attendees receive a special registration code Preconferences: 3 preconferences on Silverlight, F#, and Agile Development Keynote: Tim Huckaby will discuss "Your Development Happy Place" Register soon to get a great deal - the conference is right around the corner. I look forward to seeing you there!

In an ideal world, the source system of your data warehouse has the exact information that you need to populate all of the fields in your dimensions.  In a less than ideal world (also known as the real world), we need to cobble pieces of data together.  You may come across one scenario where all of your dimension and fact information is in one large table.  How do we handle this ETL in SQL Server Integration Services? It is possible to load new records into your dimensions while loading your fact.  One way would be to use a Lookup transformation to check for existence, and if the business key doesn't yet exist, insert that value, return the surrogate key, and go along your merry way.  On the other hand, if you need to use those dimensions for other fact tables, you may decide to load only your dimensions first. To load all dimensions from one table, we can utilize the Aggregate transformation in SSIS, which provides the capability to perform aggregations on ...

When building a SQL Server Integration Services package, many business scenarios call for inserting a record into a table if it doesn't already exist. The most commonly used database destination, the OLE DB destination, looks as though it can handle this through the SQL command Data access mode. Unfortunately, SSIS appearances can be deceiving... In the OLE DB Destination, setting the Data access mode to SQL command causes a SQL command text window to appear. You can perform typical SSIS SQL statement actions, such as building the query through the Graphical Query Designer, importing the SQL from an external file, or parsing the query. Note that there is no option to specify query parameters on this display. You will not need to set query parameters because the OLE DB Destination uses this SQL statement to find the metadata of the desired insertion table. Even adding a WHERE clause to filter the data will not change the outcome of the result. All rows passed through the D...

I had the honor of being invited to join Greg Low , fellow Solid Quality mentor, on an episode of SQL Down Under .  On last week’s episode, we discussed the new features of SQL Server Reporting Services 2008 and best practices for all versions of Reporting Services.  You can check out the episode here . During the episode, Greg and I talked about reports that can be used to monitor Reporting Services.  You can find these reports on CodePlex .  Also, the design book that I mentioned is called Information Dashboard Design .  I hope you enjoy the episode and these resources!

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