Sunday, November 2, 2008

Jessica's BI Tour

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

Tuesday, October 21, 2008

Manipulating Excel Spreadsheets in SSIS

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 As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

wb = excel.Workbooks.Open("C:\\TestExcelSS.xlsx")


End Sub

End Class

You'll see error squiggles, but don't worry about them because they will disappear in just a minute. Save and close your package. In your Solution Explorer, right click on the package and select ‘View Code’.

In the resulting XML, change the Build Settings ReferencePath property to:

Also change the Build References to include:
Name = "Microsoft.Office.Interop.Excel"
AssemblyName = "Microsoft.Office.Interop.Excel"

Save the XML, and reopen the package. Open the script task and select ‘Save’. This will compile the code, and now you can run your package.

When working with COM references, you can use the script task GUI to add the reference by adding the desired component to the .NET framework folder. I could not find Microsoft.Office.Interop.Excel.dll on my machine to move to the framework folder, which is why we added the reference through the XML.

As Douglas Laudenschlager notes, writing server-side code to access client-side Office is unsupported. Please take these possible problems under advisement and code as necessary. You have been warned. :)

Update (11/12/08): Added last two lines to code to stop Excel process.

Versions: Microsoft Office 2007, SQL Server 2005 SP2

Sunday, October 12, 2008

IIS 7.0 Role Service SSRS Requirements

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

Wednesday, October 8, 2008

Microsoft MVP

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 geeking out about SQL Server, please contact me at jmoss at solidq dot com.

Thank you.

Friday, October 3, 2008

Richmond and Olympia

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!

Monday, September 29, 2008

MDX Query Returns Duplicate Values

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’ measure group, we can see the way the new display looks, including no Customer dimension!
A user can still use the inappropriate dimension, but hopefully this will prevent the issue right from the start. Happy querying!

Version: SQL Server 2008 RTM

Thursday, September 25, 2008

PASS Community Summit 2008

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: So now you have no excuse left to not sign up!

Sunday, September 14, 2008

Display Top N Rows - Alternate Method

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(FactResellerSales.SalesAmount) AS SalesAmount
FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
WHERE (FactResellerSales.OrderDateKey = 20011001)
GROUP BY DimEmployee.FirstName, DimEmployee.LastName
ORDER BY SUM(FactResellerSales.SalesAmount) DESC
Assign the @TopN query parameter to use the @NumberPpl report parameter.

Note that this differs from the original query in two ways:
A. Includes the TOP clause to restrict the rows
B. Includes a descending order by for the SalesAmount to ensure the highest SalesAmounts are shown

Also note that the TopN query parameter is cast to an integer. This is because all query parameters are created as string and the TOP clause expects an 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’. This sort is now only for display purposes and is not necessary to return the correct data.

4. Preview the report, modifying the value for the parameter NumberPpl to see the number of rows change. It should look similar to the report below and the reports using the first method.

Version: SQL Server 2008 RTM

Tuesday, September 9, 2008

Upcoming Events

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!

Sunday, August 17, 2008


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: 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 application, it becomes an icon in the system tray instead of showing up on the task bar.

I use this application when I write all of my custom code, especially in Reporting Services. It allows me to ensure my syntax is correct, as well as keep consistent formatting before copying the code into my package or report. I hope you find this tool useful as well!

Thursday, August 14, 2008

Display Top N Rows

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
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 of rows change. It should look similar to the reports below.

Version: SQL Server 2008 RC0

Friday, August 8, 2008


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!

Wednesday, August 6, 2008

Overlapping SSRS Report Items

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 report in the Report Preview pane will give you a pretty good idea of how the report will look in HTML. I did not find this to be the case for the "Hard Page-Break Renderers", including Acrobat (PDF) format.

If you do have overlapping report items, the item to the "front" of the report will be rendered above the item to the "back" of the report. You can take advantage of this by right-clicking on the item and selecting either the option "Bring to Front" or "Send to Back" for the desired result.

For more information on how rendering works in SSRS 2008, I would recommend visiting this page:

Versions: SQL Server 2005 SP2, SQL Server 2008 RC0

Thursday, July 31, 2008

Reporting Services 2008 Configuration Mistake

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.

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 SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles. Under the latest ReportServerService*.log, there was the following error: "Message: No DSN present in configuration file". Looking at the file: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config proved the error message true, as right at the top of my log file was the empty Dsn tag.

Looking in Books Online under the RSReportServer Configuration article, I discovered that the Dsn property contains a connection to the report server database. That's odd, I don't remember creating that connection...

I returned to the scene of the crime, the Configuration Manager. Sure enough, the third warning-less menu option allows you to set up the connection to either an existing report server database or create a new one for this instance. I filled in the appropriate information, and took a look back at the configuration file. This time, the Dsn tag contains a beautifully encrypted blob of information. The report manager works, and I am ready to manage my reports! The moral of this story is to read the directions before clicking through configuration managers, don't trust warning icons or lack thereof, and don't forget that Reporting Services needs to know what database to use. :)

Version: SQL Server 2008 RC0

Monday, July 21, 2008

Where is my Data Tab?

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 control that provide you with the expected layout. As soon as that control has landed on your Design window, the properties and group menus look the same. In fact, the menu option to view properties is labeled “Tablix Properties…”. It is easy to see how the rows and columns are utilized from the icons displayed to the left or top of the textboxes.

Instead of Groups being included in the properties window of each control, they are now displayed on the report itself. This option can be toggled by right-clicking on the report and selecting View > Grouping. By selecting the desired control in your design window, you can immediately see the groups for that control.

All of the property windows have been overhauled. Is it just me, or do the new property windows remind you of the Dundas Controls property windows?

I’ll be posting more thoughts and trials on Reporting Services 2008 as I dig deeper into the new system!

Version: SQL Server 2008 RC0

Tuesday, July 8, 2008

Software Development Meme

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 first job was a consultant at a custom software development firm. I was fortunate enough to dabble in all sorts of different languages, but the first project that was entirely my own was creating Reporting Services reports to analyze sales data. Do you think they knew something about where I'd end up? ;)

If you knew then what you know now, would you have started programming?
Definitely. I originally fell in love with programming because of the logic. A + B = C and such... With time, I realized that programming is more of an art. Who knows what my next revelation on programming will be?

If there is one thing you learned along the way that you would tell new developers, what would it be?
You can't know everything. Always learn and always ask questions. If you think you know everything and you have nothing left to learn, find another job.

What’s the most fun you’ve ever had ... programming?
Hmm… college all nighters with copious amounts of mountain dew and loud music… Working on teams of people where everyone just meshes and the final program is just beautiful… That final “Ah ha” moment when something just clicks… Oh, I can’t decide!

Who are you calling out?
Trying to find people on my blog list that haven't yet been tagged!

Rushabh Mehta
Wes Dumey
Andy Warren

Monday, June 23, 2008

Thoughts on Mentoring

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 parenting. The next sections of TEoM show all phases of a mentoring relationship from picking the protégé/mentor through ending the mentorship. One recurring theme in this section is to ensure that both parties are fully aware of what they are agreeing to by entering into this relationship. I would recommend that both people start out by reading this book. It would provide a common base from which to build schedules, expectations, and timelines.

As expressed in TEoM, I think it’s important to realize that being involved in a mentoring relationship is not for everyone. Just because someone is intelligent and well-respected in their field does not mean they will make a good mentor. Just because they are a good mentor does not mean they will be compatible with every protégé’s goals and personality. Just because a mentoring relationship starts out well does not mean that it will end well. Barring the difficulties in making a successful match, if it can be done, mentoring or being mentored is definitely worth it. Getting involved in a mentoring relationship from either the protégé or mentor side can be a very fulfilling role and can create a relationship that will last long past the end of the mentorship.

Thursday, June 19, 2008

VanPASS User Group

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!

Tuesday, June 17, 2008

File Path Expression Tip

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] +
    (RIGHT(@[User::FilePath], 1) == "\\" ? "" : "\\") +

This is just a fun little tip to help you in your development!

Version: SQL Server 2005 SP2

Tuesday, June 10, 2008

Small Pleasures

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 window to find your source query.

I know RC0 just came out, but I haven't the time yet to download and play with it. I promise not to post anything more based on CTP6 after this post!

Version: SQL Server 2008 CTP6

Tuesday, June 3, 2008

Pipeline Parallelization

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:
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" (52)
    input "Flat File Destination Input" (64); component "Flat
      File Destination" (63)
  End Subpath 0
  Begin Subpath 1
    output "Multicast Output 2" (69); component "Multicast" (52)
    input "Conditional Split Input" (57); component
      "Conditional Split" (56)
  End Subpath 1
End Path 0

Begin output plan
  Begin transform plan
  End transform plan
  Begin source plan
    Call PrimeOutput on component "Script Component" (29)
      for output "Output 0" (32)
  End source plan
End output plan

Begin path plan
  Begin Path Plan 0
    Call ProcessInput on component "Multicast" (52)
      for input "Multicast Input 1" (53)
    Create new execution item for subpath 0
    Create new execution item for subpath 1
    Begin Subpath Plan 0
      Call ProcessInput on component "Flat File Destination" (63)
        for input "Flat File Destination Input" (64)
    End Subpath Plan 0
    Begin Subpath Plan 1
      Call ProcessInput on component "Conditional Split" (56)
        for input "Conditional Split Input" (57)
    End Subpath Plan 1
  End Path Plan 0
End path plan

You can see that the new log creates a subpath for both the flat file destination path and the conditional split path. The best part is that each subpath can then be executed on different processors, increasing the speed of the package!

Version: SQL Server 2008 CTP6

Thursday, May 29, 2008

SSIS Configuration to Configuration to Configuration Schema

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"><configuredvalue><configuration configuredtype="Property" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" valuetype="String"><configuredvalue>Data Source=EnvironmentServer;Initial Catalog=SSIS;Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;</configuredvalue></configuration>
where “EnvironmentServer” is replaced with the appropriate server for that environment.

3) On the EnvironmentServers, create a table called SSIS.dbo.SSISConfigurations with all connection strings and values that you could want to use in your packages with the ConfigurationFilter "CommonConfigurations". You can create this table by opening up a package, creating a SQL Server configuration, and select the “New” button next to the Configuration Table option.

4) In your package, create an OLE DB connection manager named CONFIG_SERVER with dummy server and database information.

5) In your package, create an XML configuration file Package Configuration with the option selected “Configuration Location is stored in an environment variable” and select the environment variable SSIS_CONFIG_FILE. If the variable does not exist, close and reopen BIDS. Then (and make sure this is the second configuration entry), create a SQL Server Configuration that uses the CONFIG_SERVER connection manager and the “CommonConfigurations” filter.

6) For any new connection managers that you create, as long as you follow the naming convention of the connection managers that are already in the SSISConfigurations table, the connection string will automatically be loaded for use in your package!

All of the names and locations can of course be replaced by something more relevant to your servers. They can even be different on each server, just as long as the name of the environment variable is the same on all servers.

By using this schema, you reap many benefits. One perk is allowing you to move your packages through environments without having to change the package or the configuration file. You also are encapsulating all of your connection information in one database table, letting you secure private information. Finally, you are reducing the development time of your packages because by you don’t need to recreate the configurations to all servers; they will already be available for your package.

Version: SQL Server 2005 SP2

Monday, May 26, 2008

Calling all Girl Geeks!

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:


I know I'm looking forward to it!

Saturday, May 24, 2008

Delimited String Custom Data Flow Components on CodePlex

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:



Take a look at the projects and let me know if you have any comments, suggestions for improvement, or if you find them useful!

Tuesday, May 20, 2008

SQLTeach Toronto Wrap Up

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 learning about this exotic new land of acronyms. I am interested in looking more into how these concepts are applicable to developing Business Intelligence applications.

5) Who knew that talking for 2.5 hours straight would make me lose my voice? :)

Overall, I had a great time. I met some great people, both speakers and attendees. As always, I learned a lot, and hopefully I imparted some wisdom as well. Looking forward to the Montreal conference later this year!

Friday, May 16, 2008

NoVa CodeCamp 2008.01

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!

Thursday, May 8, 2008

SSIS Custom Component Custom Property

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 code similar to:

If ComponentMetaData.CustomPropertyCollection("RemoveEmptyStrings").Value.ToString.ToUpper = "TRUE" Then
    'Perform logic
End If

This will allow the custom property to show up on the initial Edit screen of the custom component. When creating a package, the developer can select the option appropriate to their business logic, and that option will decide the course of action at runtime.

Version: SQL Server 2005 SP2, VB.NET 2.0

Wednesday, April 30, 2008

SSIS Performance Comparison

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!


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 comparison.
Package 1: Column.dtsx

1 Data Flow Task containing:

  • 1 OLE DB Source:
    SELECT top x *
    FROM [AdventureWorksDW].[dbo].[FactLottaInternetSales]
    WHERE orderdatekey=1
  • 1 Conditional Split: OrderDateKey <>
  • 1 Row Count (as a terminator)

Package 2: Variable.dtsx

1 Execute SQL Task which assigns the result to the variable DateKey:
select max(orderdatekey)
from adventureworksdw.dbo.FactLottaInternetSales
where orderdatekey=1

1 Data Flow Task containing:

  • 1 OLE DB Source:
    SELECT top x [ProductKey] ,[OrderDateKey] ,[DueDateKey] ,[ShipDateKey] ,[CustomerKey] ,[PromotionKey] ,[CurrencyKey] ,[SalesTerritoryKey] ,[SalesOrderNumber] ,[SalesOrderLineNumber] ,[RevisionNumber] ,[OrderQuantity] ,[UnitPrice] ,[ExtendedAmount] ,[UnitPriceDiscountPct] ,[DiscountAmount] ,[ProductStandardCost] ,[TotalProductCost] ,[SalesAmount] ,[TaxAmt] ,[Freight] ,[CarrierTrackingNumber] ,[CustomerPONumber]
    FROM [AdventureWorksDW].[dbo].[FactLottaInternetSales]
    where orderdatekey=1
  • 1 Conditional Split: @[User::DateKey] <>
  • 1 Row Count (as a terminator)


The variable package takes longer up to about 2 million rows. After that, the column package takes longer! Happy performance tuning :)

Version: SQL Server 2005 SP2

Monday, April 28, 2008

Come to SQLTeach Toronto!

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!

Thursday, April 24, 2008

Women Technical Speakers

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.


Disclaimer: You'll see my bio on there too :)

Tuesday, April 22, 2008

Execute SQL Task Designer Limit

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, the task also works.

This is just something to keep in mind if you are putting a monster of a query together to execute in SSIS. Of course, if you have a query that long, you might want to think about breaking it up into smaller pieces or encapsulating some of the logic into stored procedures. :)

Version: SQL Server 2005 SP2

Wednesday, April 16, 2008

Show SSIS Packages Alphabetically in SSMS

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
[folderid] = @folderid


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 Server 2005 SP2/SQL Server 2008 CTP6

Wednesday, April 9, 2008

Orphaned Log Records

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 (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 will occur before the PackageStart event. Because most logging is done based on the PackageStart event, these "orphaned" records will not show up any reports.

To see these records, you can run the following query:
select * from sysdtslog90

where executionid not in
( select executionid from sysdtslog90
where event in ('PackageStart', 'PackageEnd') )
and executionid in
( select executionid from sysdtslog90
where source = 'putPackageNameHere')

You can also use a similar query to delete these records if you don't want them cluttering up your log table.

Version: SQL Server 2005 SP2

Thursday, March 27, 2008

Migrating the Dynamic Property Task

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 TypeSSIS Functionality
INI FilePackage Configuration
QueryExecute SQL Task, Variable, Expression
Global VariableExpression
Environment VariablePackage Configuration
Data FilePackage Configuration

Good luck in your migration!

Version: SQL Server 2000 SP4, SQL Server 2005 SP2

Tuesday, March 18, 2008

InteractiveMode Variable

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 one user that just can't give up their precious message boxes, you can use this code:

If CBool(Dts.Variables("InteractiveMode").Value) = True Then
MsgBox("This is a message box!")
End If

This will allow the user to see their message box on the client, but repress the error when running on the server!

Version: SQL Server 2005 SP2

Wednesday, March 12, 2008

Anything technical that piques my interest

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!