Here we are with already the sixth installment of our Belgian Community Day.

To please everyone, there are 6 different tracks with over 40 sessions!  As you’re reading my blog, you’re probably most interested in what sessions the SQLUG has got to offer.  Here they are:

When? Thursday June 21st 2012

Where? Utopolis Mechelen, Spuibeekstraat 5, 2800 Mechelen

Price? Nada, niente, nougabolle! (yes, it’s still free)

Of course there are many more sessions, some of them even related to SQL Server, such as this one by the CLUG: Business Intelligence with SharePoint and SQL Server 2012: what’s new by Serge Luca and Isabelle Van Campenhoudt. 

And that’s not all, here’s one more by the BIWUG: SQL Server for SharePoint geeks and other involuntary DBA’s by Thomas Vochten.

I’m also looking forward to the session on (M)ogre 3D game development by my Ordina-colleague Mario Van Hissenhoven.

So, convinced? Register now without hesitation because places are getting filled really quick!

Have fun!

Valentino.

The resulting DTSX package and input files can be downloaded from my SkyDrive through this link.

Dealing With Special Characters

If you’ve followed the instructions in my article mentioned above and you need to deal with special characters such as the é and è encountered in the French language, you probably noticed that it wouldn’t really work as expected.  In fact, in your final result you may have ended up with the special characters being replaced with other, even more special, characters.  Obviously not good.

Here’s an explanation on the reason why that happens, and also how to deal with it.

Setting The Scene

Imagine the following sample XML, representing a really huge book collection:

<books>
    <book>
        <title>The Hitchhiker's Guide to the Galaxy</title>
        <author>Douglas Adams</author>
        <language>EN</language>
        <description>The Hitchhiker's Guide to the Galaxy is a science fiction comedy series created by Douglas Adams.</description>
    </book>
    <book>
        <title>Le Trône de fer</title>
        <author>George R.R. Martin</author>
        <language>FR</language>
        <description>Le Trône de fer est une série de romans de fantasy de George R. R. Martin, dont l'écriture et la parution sont en cours. Martin a commencé à l'écrire en 1991 et le premier volume est paru en 1996. Prévue à l'origine comme une trilogie, la série compte désormais cinq volumes publiés et deux autres sont attendus.</description>
    </book>
</books>

As you can see, the second book in the list is the French version of the first book in the A Song of Ice and Fire series by George R.R. Martin and as it goes with French, there are some accents in the description of the book.

We’ll use the following XSLT to convert it to CSV:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs=http://www.w3.org/2001/XMLSchema xmlns:fn="http://www.w3.org/2005/xpath-functions">
  <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
  <xsl:template match="/">
    <xsl:text>BookTitle;Author;Language;Description</xsl:text>
    <xsl:text>&#13;&#10;</xsl:text>

    <xsl:for-each select="books/book">
      <xsl:text>"</xsl:text>
      <xsl:value-of select="title"/>
      <xsl:text>";"</xsl:text>
      <xsl:value-of select="author"/>
      <xsl:text>";"</xsl:text>
      <xsl:value-of select="language"/>
      <xsl:text>";"</xsl:text>
      <xsl:value-of select="description"/>
      <xsl:text>"</xsl:text>
      <xsl:text>&#13;&#10;</xsl:text>
    </xsl:for-each>

  </xsl:template>
</xsl:stylesheet>

Using an XML Task in the Control Flow, as explained in my article, we’d get the following output:

BookTitle;Author;Language;Description

"The Hitchhiker’s Guide to the Galaxy";"Douglas Adams";"EN";"The Hitchhiker’s Guide to the Galaxy is a science fiction comedy series created by Douglas Adams."

"Le Trône de fer";"George R.R. Martin";"FR";"Le Trône de fer (A Song of Ice and Fire) est une série de romans de fantasy de George R. R. Martin, dont l’écriture et la parution sont en cours. Martin a commencé à l’écrire en 1991 et le premier volume est paru en 1996. Prévue à l’origine comme une trilogie, la série compte désormais cinq volumes publiés et deux autres sont attendus."

So far so good, all accents are still present!

Then we’d import the file using a Flat File Source component in a Data Flow Task.  Here’s what the General page of the Flat File Connection Manager would look like:

We’ve set double-quote as Text Qualifier and checked the Column names in the first data row textbox.

Switching to the Columns page we’d get the following:

Hang on, that’s not right!  The Preview is not displaying our accents as expected!  Oh my, what’s going on here? Let’s call the code page detectives!

A Mismatch Investigation

Take a good look at the XSLT which we’ve used to convert the XML into CSV, especially the xsl:output line:

<xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>

That line specifies that the text output should be encoded using the UTF-8 code page.

Now take a good look at the General page in the screenshot earlier, more precisely this part:

Indeed, code page 1252 (ANSI – Latin I).  While the input is UTF-8.  Of course that results in a mismatch of certain characters, as demonstrated here.  The fix is fairly easy, just change the Code page setting to 65001 (UTF-8).

If we now switch back to the Columns page we should come to the following result:

Ah, sure looks better doesn’t it?  All accents are present as expected.

But in case you thought that’s it, I’d advise you to think again.  Don’t worry, I’ll demonstrate what I mean.  Let’s do that by setting up a simple Data Flow.

Setting Up The Data Flow

Throw in a Flat File Source and specify our Flat File Connection Manager.  I also prefer to keep NULLs as they come in, using the Retain null values from the source as null values in the data flow checkbox.

If you click the Preview button you should get similar output as shown one screenshot earlier.

Now hook this up to an OLE DB Destination that writes the incoming data into a table in your favorite database:

As you can see, our destination is not entirely happy with all this.  Here are the details of one of the error messages:

Validation error. Data Flow Task: Data Flow Task: The column "BookTitle" cannot be processed because more than one code page (65001 and 1252) are specified for it.

Looks like once more we’ve got a code page conflict.  And we sure do. Clicking the Data Flow connector between the Flat File source and OLE DB destination shows us the following:

Each of our incoming string values is encoded using the 65001 (UTF-8) code page.  But our database was created using the Latin1_General_CI_AS collation.  So we’ve indeed got a code page conflict!

Fear not, that’s easily remedied.  Add a Derived Column transformation in between the source and destination and convert each incoming string value using a cast expression such as this one:

(DT_STR, 50, 1252)BookTitle_IN

Note: whenever I need to manipulate incoming columns to create a second version of the same column, I rename the incoming column to TheColumn_IN.  The new version will be called TheColumn and preferably TheColumn is the name of the field in the destination table.  This makes it easy to distinguish all columns later down the flow.

Here’s what the final version of the Derived Column looks like:

Next we’ll need to open the Destination and change the mapped fields to the new ones.  Because my new columns are called exactly the same as the fields in the destination table, I can do that easily.  In the Mappings page, all I need to do is right-click the grey background in between the two tables and click Select All Mappings, hit the Delete button, right-click again and click Map Items By Matching Names:

With the data flow finished, let’s give our package a run!

Ouch, our source is not happy!  A closer examination of the Output pane brings us to the following error:

Error: 0xC02020A1 at Data Flow Task, Flat File Source [16]: Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Oh right, so far we haven’t bothered looking at the actual length of the data that we’re importing.  Actually, what is the length of our data flow columns??  Well, if you’ve been paying close attention you should have noticed the number 50 several times in the screenshots and expressions above.  That’s indeed the default length for text columns when importing a flat file.

And if you scroll back up to the sample XML, you’ll notice that the content for the description is longer than 50 characters, thus causing our error!  Let’s find out how to get that solved!

Fixing The Field Length Issue

The first step in getting this fixed is opening up the Advanced page in the Flat File Connection Manager editor.

Then select the Description field and change its OutputColumnWidth property from 50 to 500.

That will cause the source to generate a warning.  Remove this warning by opening and closing the source editor.  Click the Yes button in the popup that appears.

The next step is changing the expression for the Description field in the Derived Column to this:

(DT_STR,500,1252)Description_IN

Indeed, the field length is one of the parameters in that cast.  The other numeric parameter is obviously the code page.

Having done that you’ll notice that the destination will start complaining.  Of course, you’ll need to adapt the destination table to reflect the field length increase as well.  So change the table definition and open/close the destination editor to make it happy.

Alright, let’s run the package once more!

Finally the data flow is happy with it all and has inserted two records:

And what does our table contain?  Let’s find out:

That’s looking good for sure!

Conclusion

In this follow-up article I have demonstrated what might go wrong when you need to deal with special characters while importing flat files, and how to solve your possible issues.  In case you missed the original article, have a look through this link.

Have fun!

Valentino.

References

Wikipedia: UTF-8

In other words: Rafal is once more in our small country!  Next Thursday he’ll be talking about the present and future of the Microsoft Business Intelligence stack.

Location: Utopolis Mechelen, Spuibeekstraat 5, Mechelen, Belgium.

Date/Time: Thursday April 26th, 0900 – 1700.

Check out the Belgian SQLUG site for more details.

Ow, and it’s a full-day free event, so what are you waiting for?  If you’re into BI, you’d better have a real good excuse to miss this opportunity!  Register now!

See you there!

Valentino.

Let’s find out how such a database project can be created!

Getting Started With SSDT

Obviously the first step is installing SQL Server 2012 and making sure to select SQL Server Data Tools in the Feature Selection page:

With that up and running, launch the SQL Server Data Tools from the Microsoft SQL Server 2012 folder in the Start menu and go to File > New Project.

One of the template categories is called SQL Server.  Under that you’ll find a template called SQL Server Data Tools – Database Projects (Web Install).

Erm, hang on, what do you mean, “web install”?  Does that mean it’s not installed yet?  The description on the right also gives some clue to what’s going on: “provides instructions for installing SQL Server Data Tools – Database Projects from the web”.  Okay, let’s get on with it then!

Click the OK button to proceed to the installer.

Installing the SSDT Database Projects template

I think the message in that window above is a bit misleading.  It says that “Database Projects” is required, but isn’t that what we’re going to install now?  Sounds like chicken and egg to me!  Let’s just ignore the message then and click the Install button.

We’re now presented with a web page in Internet Explorer.  In this Data Developer Center, click the blue Download SQL Server Data Tools link.

Next, when presented with the following pop-up, click Allow:

The next window is the Web Platform Installer 3.0 that wants to install the Microsoft SQL Server Data Tools.  Click the Install button to get to the next step.

As usual with any installer from Microsoft, we need to accept the license terms so click the Accept button.

Finally, the installation begins!

To keep us busy, we’re presented with another pop-up with a progress bar:

Woah!  Is this thing installing SQL Server 2012 Express LocalDB like it says in the message?  I didn’t ask for that!  MS people, as I already have a version 2012 DB engine running on my machine, please make this optional…

Just a little later we’re presented with this:

Clicking the Finish button gives us yet one more pop-up:

The Web Platform Installer presents us with a list of applications we can install, including Microsoft SQL Server Data Tools.  Oh my, I thought we just finished installing it?  Let’s not be silly and click the Exit button

To conclude, we need to restart Visual Studio 2010, or aka SSDT.

Really Getting Started With SSDT

With everything up and running smoothly this time, open SSDT once more and in File > New Project you’ll now find the SQL Server Database Project under the SQL Server collection:

If you also have Visual Studio 2010 installed in Premium or Ultimate edition, you should take care to open up the correct SQL Server template collection.  The reason for that is because the data dude projects are included in those versions of Visual Studio, as shown in the screenshot below:

So the templates under Database > SQL Server are not what you’re looking for!

That’s it for now, I’ll demonstrate my favorite SSDT feature in an upcoming post!

Have fun!

Valentino.

References

SSDT Main page on Data Developer Center

SQL Server Data Tools Team Blog

Huh, the what?  I know INDEX and I know CLUSTERED INDEX.  Heck, I even know the filtered index, but COLUMNSTORE?  That’s new to me!

If that’s your reaction, or you simply want to find out how this new SQL Server 2012 feature has been used in one of the largest data warehouses in the country, you should be present next Monday for our upcoming SQLUG event.

Location: Microsoft Belgium, Corporate Village, Leonardo Da Vincilaan 3, 1935 Zaventem

Presenter: Ludo Bernaerts (ITN Senior Operational Engineer at Belgacom)

Date: 16/04/2012

Time: 1800 – 2100

Ow yeah, registration goes through here!

See you there!

Valentino.

I have a report and want to show A, B, C but sometimes I want X, Y, Z and not A, B, C. How?

Or, in other words:

I have a parameter and based on the selection I want to return different fields in my dataset.  How can I do that?

My initial reaction would be “I don’t think you can do that”.  But then I thought it would be useful in certain situations and decided to try it out. And guess what?  You can do that!  Here’s how.

Just to make sure everyone is on track: this article is not about dynamicity in terms of rows returned, that would be filtered datasets and you’ll already find plenty of references on the internet.  This article is about a varying number of columns in the dataset, which is a little less straightforward.

The database used in the examples is AdventureWorks2008R2, available at Codeplex.  And the screenshots are taken from SQL Server 2008 R2 x64 SP1.

The Scenario

The report we’re going to build should show a list of products sold.  But the report is used by different user groups: those who just want to see the sales numbers and those who also care about stock levels!

So by default the report should show a list of items and number sold, but it should also be possible to render that same report whilst displaying the stock-related statistics.

And to make it even a bit more interesting, by default the data should be ordered according to product number but in “stock level mode” the ordering should put those with the lowest current stock first.

The Report

Let’s get started!  The first step in creating a report is often the writing of a SELECT statement.  In this scenario we’ll be needing two of them, both in the same dataset.

The Dataset

Our dataset is going to need a parameter to be able to decide what type of user is requesting the report. Let’s call that parameter WithStockData, and its type will be Boolean.

The layout of the dataset statement will be this:

if @WithStockData = 1
-- sales and stock data
else
-- regular sales data

A simple IF statement, taking the parameter into account.  The parameter is a boolean value so when it’s True, it equals to 1.

And here’s the full statement for the dataset:

if @WithStockData = 1
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc
else
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc;

Some data as returned by the SELECT in the then part:

And some data as returned by the query in the else part:

As you can clearly see, the first query returns seven fields while the second one contains only three.  You can also see that both results are ordered differently.

Now, let’s get the fun started!  Create a new report, set up a data source that points to the AdventureWorks2008R2 database and create a dataset with the query above:

Power tip: to create the dataset, do not right-click on the Datasets node in the Report Data pane, but right-click on the Data Source and then select Add Dataset.  That saves you some work because the Data Source will be pre-populated.  All you need to do is paste the query in the Query field and give it a decent Name.

Click the OK button to close the Dataset Properties.

Now open the new dataset in the Report Data pane and count its fields:

You should come to seven!  How nice, all of our fields are there.  This is not always the case, but I’ll handle that later.

The Parameters node in the Report Data pane should now contain a new parameter called @WithStockData:

Double-click it to get to its properties and change the Data type to Boolean (by default it’s Text).

If you want, you can also specify a default value.  The value should be either “true” or “false”:

With the dataset fully set up, let’s now move on to visualizing it.

Displaying Dynamic Columns

Put a Table on the report Design and set it up as follows:

All seven columns have been added, and to avoid whitespace in the table when some columns are hidden I’ve put the dynamic columns at the end.

One step remains: even though the values won’t always be present, the columns will not disappear automatically.  To take care of that, we’ll enter an expression on the Hidden property of each column.  Each column in a dataset has got the IsMissing property.  When its value is True, it means that the column is not present and should thus be hidden.

The expression looks like this:

=Fields!InventoryQuantity.IsMissing

Or visually:

To set up the expression, click the grey area above the column title to select it and then locate the Hidden property in the Properties pane.  Do this for each dynamic column (don’t forget to change the column name in the expression).

And here’s what the rendered report looks like:

Once more, with the parameter set to False:

The four stock-related columns are nicely hidden!  And the ordering is working as well because we’ve taken care of that in the dataset’s queries.

Easy, huh?  Well, yeah, but I’ve made sure that the process went as smoothly as possible.  It takes some knowledge on how SSRS actually works.  Let’s make this clear by adapting the example just a little.

Understanding The Dataset

Delete the current dataset and create a new one, using the following statement (ensure that you give it the same name as the original one):

if @WithStockData = 0
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc
else
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc;

The only difference with the previous version is that the IF condition is reversed and thus the two queries are swapped.

Now render the report.  What do you see?

An error occurred during local report processing.

The definition of the report ‘/DynamicDataset’ is invalid.

The Hidden expression for the text box ‘Textbox7’ refers to the field ‘InventoryQuantity’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Letters in the names of fields must use the correct case.

Oh my, it’s broken!

Now take a good look at the available dataset fields:

That’s right, only three!  Four of them have gone missing!  The reason for that is because SSRS uses the first SELECT query it encounters in the whole statement to determine the available fields.  It’s not able to automatically detect the different situations and create all the fields that can possibly be returned.

One way to ensure all fields are created is to put the query that returns all possible fields as first query, which is what I initially did.  But of course that’s not always an option.

Manually Adding Fields To A Dataset

Luckily it’s possible to manually add fields to the dataset.  You can do this by clicking the Add button in the Fields page of the Dataset Properties and then selecting Query Field.

So, add the four missing fields:

If you now render the report, it should behave exactly the same as in the initial version!

What About Those Warnings?

If you’re someone who pays attention to the Error List pane, you may have noticed some warnings.  Two for each dynamic field to be exact.

Here’s an example of the rsMissingFieldInDataSet warning:

Warning    1    [rsMissingFieldInDataSet] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. This field is missing from the returned result set from the data source.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

And here’s the rsErrorReadingDataSetField warning:

Warning    2    [rsErrorReadingDataSetField] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. The data extension returned an error during reading the field. There is no data for the field at position 4.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

I’ve got a developer background, so I always try to remove all warnings.  So if you really want to get rid of those warnings too, even that’s possible.  But it will require some Custom Code writing.  I already covered that topic some years ago, when I wrote an article about Detecting Missing Fields.

Conclusion

With this article I believe to have demonstrated that datasets can be quite flexible, even though it doesn’t always seem so.

Have fun!

Valentino.

Apparently they removed it because the community was confused about it, hmm.  Now how can I get that back?

This device is a GPS, so technically it’s able to detect if your moving and how fast.  It can also stop/start recording automatically.  And here it comes: by default it doesn’t do that!

Now, to activate the auto pause, it would seem logical that you’d need to change a setting in the Settings menu, right?  Well, wrong!  The setting is located in the Training menu!  Inside that menu, you need Auto Pause/Lap and there you’ve got the Auto Timer Pause setting which you can change to Off to When Stopped or Custom Speed.

Tomorrow I’ll try my first track with Custom Speed set to 4 km/h!  This way I hope I avoid that it records while stopped for refueling (read: sports drink and cookies, fruit, not gas or electricity).

Need more info?  Try this video then:

Have fun!

Valentino.

As always, the first question that then comes to mind is: in what order should I install my software?  SQL Server, Visual Studio, any service packs, add-ons, …

Obviously, the basic requirement is SQL Server 2008 R2.  I also need Visual Studio 2008 for custom development, and Visual Studio 2010 for the Database Projects.  Furthermore we’re using Team Foundation Server 2010 for source control purposes.

The list below represents the order with which I’ve come up and which I’ve applied with success.  This order is based on own experience, plus some web research to make sure I didn’t miss anything crucial.

Tip: when given the chance, always go for Custom Install.

• Visual Studio 2008 (ensure to not install SQL 2005 Express Edition)
• VS2008 Team Explorer (link)
• SP1 for VS2008 (link)
• VS2008 SP1 Forward Compatibility Update for TFS2010 (KB974558)
• SQL Server 2008 R2
• SP1 for SQL2008R2 (link)
• Visual Studio 2010 (ensure to not install SQL 2008 Express Edition)
• SP1 for VS2010 (link)

In case you’re wondering why this matters so much, well, let’s just say that getting the order wrong can really cost you some time.  In the past I’ve had the pleasure of witnessing an Intellisense (in SSMS) that’s completely broken and an error in VS2010 that prevented me from using the Database Schema Comparison feature to script out the changes.

Hopefully this saves you some time, and I encourage you to have fun while you’re watching that installer’s progress bar!

Valentino.

And what would you think if SSIS offered the same possibility?  Imagine, being able to write a custom function in .NET and then use it in any expression in your package, how powerful that would be!

There’s already one function I would have written today: GetFilename(string path).

If you believe such functionality to be useful, please vote on the following Connect request: Add user defined function support to the SSIS expression language

Have fun!

Valentino.

Ever since I got a PC at my client, I was annoyed by the fact that Word would open all documents in the same window.  As a result of that, when I had two documents open, I couldn’t use ALT-TAB to switch between them.  (I prefer using my apps maximized.)  That really annoyed me.  Furthermore, each time when I clicked the closing X at the top right of the screen, it would close down all open documents instead of the one in front of me.  Aaarghh!!

And today I finally found the setting to make it behave decently.  It’s not so complicated once you know where to find it.  (In fact, it’s a simple checkbox.)

Curious?  I’ll explain how to change the setting in Word, but Excel will work just as fine.

Open the Word Options by going to File > Options.  Then switch to the Advanced page.  Locate the Display section.  In that section you’ll find a checkbox labeled Show all windows in the Taskbar.  Guess what that does?!

So that one more aggravation gone!

Have fun!

Valentino.