SQL Server

You are currently browsing the archive for the SQL Server category.

image

Regular readers of this blog know that I was one of the speakers at our Belgian SQL Server Days event last Thursday and Friday.

I have to admit that I underestimated the amount of work a little: working full time for a customer, preparing a one-hour session, preparing the SQL Server Days (luckily we’ve got a great team at the SQLUG for this part!) and still having some kind of personal life as well.

On day two we had the pleasure of welcoming 279 attendees all ready to attend sessions in our five different tracks.  And about 70 of those people were in my room!!  I had practically no stress before or during the start of my session, except for the moment when I had to get the microphone wire attached to my head somehow (luckily the technician was still around!)…  In fact, I was more stressed for my dry run session three days earlier.

Would I do it again?  Ow yeah, absolutely!  Even though it requires quite some time in preparation, the applause at the end of the session makes up for that! Smile

And now the moment you’ve all been waiting for: my slides and demos are available for download from my Skydrive.

References

For those seeking more information, here are some references related to my session content:

The official Data Quality Services blog (a lot of useful content on DQS!!)

SSIS DQS Matching Transformation

SSIS DQS Domain Value Import

Creating a Synchronous Transformation with the Script Component

Fuzzy Lookup Transformation

DQS Cleansing Transformation

Finding Similar Strings With Fuzzy Logic Functions Built Into MDS

Have fun, and see you next time!

Valentino.

Share

Tags: , ,

SQL Server Days 2013For the first time ever am I not only participating in the organization of the SQL Server Days, I’ll be speaking as well!  Exciting times!

In my session, Cleaning up the mess with SSIS and DQS, you’ll learn a couple of tricks on dealing with dirty data.

Here’s the abstract for my session:

Are you loading data from exotic sources such as Excel and flat file?

Or are you dealing with manually-entered data through an application that allows, well, practically anything?  And do you sometimes run into trouble because the incoming data is not as expected?

Then you should really join us in this session in which I’ll demonstrate (yes, demos!) several different techniques that can be used to cleanse your dirty data!

Check out the agenda for more details!

We’ve got speakers from all over the world: South-Africa, Italy, Slovenia, UK, US and of course… Belgium!

Not registered yet?  Do it now!

Ow, and did you know we’ve introduced a special feature?  It’s called Bring a colleague for free.  If you register you can register an additional person for free, as long as there aren’t 50 of those free ones registered that is so be fast!

Bring your colleague

In the meantime: have fun and see you there!

Valentino.

Share

Tags: , , , , ,

A Record SetAs you may already know, it is possible to use the Execute SQL Task to populate a package variable with a result set.

In case you’re not that familiar with this technique yet, here are a quick two words on setting that up.  You just give it a query, set the ResultSet property to Full result set and configure a package variable in the Result Set property window.  The package variable’s type is System.Object.

But what exactly is this mysterious System.Object and how can we explore it?  Well, that depends.  More precisely, it depends on the Connection Type which you’ve chosen in the Execute SQL Task properties.

Let’s explore two possibilities: ADO.NET and OLE DB.  Our end goal is straightforward: retrieve the number of records in the result set.

The query which I’m using in the Execute SQL task is this one:

select ProductAlternateKey
from dbo.DimProduct
where Color = 'blue'

On my AdventureWorksDW2012 database it should return 28 records: 28

Exploring the ADO.NET result set

The first step is finding out what type exactly this result set object is.  Hook up a Script Task to your Execute SQL task and put a breakpoint on it.  Now run your package and examine the Locals window:

Debugging the Control Flow to find the object type

Well look at that, it’s a System.Data.DataSet!  Using this knowledge it’s fairly simple to produce code that fetches the record count:

DataSet ds = (DataSet)Dts.Variables["MyResultset"].Value;
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());

Note: don’t forget to add the package variable to the ReadOnlyVariables before opening the code editor.

The System.Data namespace is included by default in the using statements, no worries there.  So we can just cast the variable into a Dataset.  The DataSet object contains a DataTableCollection called Tables.  As there’s only one result set this is located at index zero.  We travel down the object tree to finally find the Count property of the Rows DataRowCollection.

And here’s the result:

The message box shows 28 items

That’s all there’s to it, easy huh?  Let’s move on to our second option, OLE DB.

Exploring the OLE DB result set

Once again we start at the beginning: with the debugging of the Control Flow to find out what object type our mysterious System.Object is:

The OLE DB result set gives us a System.__ComObject, hmm...

Hmm, System.__ComObject, that’s … special.  Ow right, the OLE DB provider uses a COM wrapper.  How can we “unwrap” our object and introduce it to the .NET world?  Let’s see if we can find out what’s hidden behind that wrapper, by using the following code:

MessageBox.Show(Microsoft.VisualBasic.Information.TypeName(Dts.Variables["MyResultset"].Value));

TypeName is a VB.NET function and retrieves the data type of the parameter passed into it.

To get this to run in a C# SSIS task you first need to add the Microsoft.VisualBasic reference:

Adding a reference to the VB.NET assembly

Executing the package results in this:

Result type: Recordset

So, our result is Recordset, hmm, well, I think we more or less knew this already.  What kind of Recordset?  Well, an ADO Recordset.  We know this because the following code works:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["MyResultset"].Value);
MessageBox.Show(dt.Rows.Count.ToString());

Basically, we use the Fill method of the OleDbDataAdapter to fill a System.Data.DataTable with the data from the ADO Recordset.  The version of the method in our example (there are several overrides) accepts two parameters:

public int Fill(

DataTable dataTable,

Object ADODBRecordSet

)

With the DataTable filled we’ve got once again access to a Rows DataRowsCollection, exactly the same as in our ADO.NET example in fact.  Executing the package now results in exactly the same message box as shown earlier: 28 records!

Beware of pitfalls

If you mix the two methods up you’ll get funky errors such as:

System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

and also

System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.

So be careful, use the right object types for your particular System.Object.

Conclusion

In this article I’ve demonstrated a couple of methods which can be used to retrieve information from the mysterious System.Object result set in an SSIS package.

Have fun!

Valentino.

Additional References

Execute SQL Task

Result Sets in the Execute SQL Task

OleDbDataAdapter Class

Share

Tags: , ,

About half a year ago I wrote an article in which I explained how you can get newsletter-style reports implemented which work in every renderer (except just the image ones), I called that article Creating Multiple-Column Reports.  And last week that article received an interesting comment, a comment which requires a bit more than just a couple of lines in reply.  So I decided to write an article instead!

I’ll be using the same technique as in my previous article but the output (and part of the technique) will be different.  So I will not go into all detail on the parts that overlap.  If something in this article isn’t clear, please have a read through my previous article first.

The resulting RDL can be downloaded from my Skydrive.

The Scenario

Just like in my previous article the data should get rendered over multiple columns, in the example I’ll implement two columns but the technique can be used easily for more columns as well.  However, the difference is this: instead of spreading the data horizontally, from left to right, it should spread vertically, from top to bottom!

The Query

Let’s start with a really simple query:

select DP.ProductKey, DP.EnglishProductName
    , COUNT(*) over () RecordCount
from dbo.DimProduct DP

When executed against the AdventureWorksDW2012 database it gives something like this:

Output of my simple query

What you see is a list of products.  The last column, RecordCount, has been added by using the OVER clause and represents, guess what…, a record count.  It will become clear later on why this is needed.  In case you’re not familiar with the trick with the OVER clause, have a look at this article: Aggregating Data With The OVER Clause.

The Tablix

Start by adding an empty Table to the report and remove one of the columns.  Also remove the header line and the default (Details) Row Group:

Empty table without row group

Connect the table to the dataset by setting the DataSetName property.

Nest another Table inside the left cell of the main table.  This is explained in detail in my other article, if needed.

Nesting a tablix inside another tablix

Open up the properties of the nested tablix and switch to the Filters page.

Filtering the tablix to show only the first 50% of records

The expression in the Value box is this:

=Fields!RecordCount.Value / 2

As you can see, we’re using the count of records to decide which records should be shown.  As we want half of them, we’re dividing by two.  This also assumes that the dataset contains a field with an increasing number.  Out of convenience, I used the ProductKey from the DimProduct table.

That’s the left column completed.  Now select the nested tablix and copy/paste it into the remaining empty cell on the right of the main tablix.  Open up its properties and switch to the Filters page.

Filtering the remaining 50%

Change the Operator as shown in the screenshot, > instead of <=.

Let’s render that report!

Data rendered in two columns, top down!

Looks good doesn’t it?  We’ve got 606 records and the first item in the second column is 304!

Conclusion

In this spin-off on my previous article on rendering data over multiple columns I have demonstrated a method which can be used to render the data top-down and spread over two columns.

Have fun!

Valentino.

Share

Tags: , , ,

Date and TimeIn my previous article I discussed all possible methods to get numbers formatted any way you prefer.

But I didn’t mention a special type of number: date and time values!  So let’s look into these now.

The Format Property

Just as with regular numbers we can use the Format property of the SSRS textbox to tell the report what the output should look like.  But what do we need to type in there?  Well, once again there are some predefined date/time formats which can be used here.

The table below shows a couple of examples of how to format the output of the Now() function:

Formatting Now() through predefined formats and the Format property

And if those predefined formats are not sufficient for your requirements then, guess what, there are some user-defined formats which can be used instead.  Some examples:

Formatting Now() through user-defined formats and the Format property

The FormatDateTime Function

What if you don’t want or just can’t use the Format property?  No worries, in that case you can rely on a couple of functions.  The first one is the FormatDateTime function.

Here’s what it’s definition looks like:

Function FormatDateTime(
   ByVal Expression As DateTime,
   Optional ByVal NamedFormat As DateFormat = DateFormat.GeneralDate
) As String

The first parameter should be obvious, it’s the DateTime to be formatted to string.

The second parameter expects a value of the DateFormat enumeration.  Here are the possible settings and their corresponding output:

Using the FormatDateTime function

The Format Function

Alright, so what if that FormatDateTime function is still too limited?  Then rely on the Format function!  Some examples:

Using the Format function to format a datetime value

As you can see, all possible strings that you can put in the Format property will work here as well.  In fact, the links I gave you earlier lead to subpages of this Format function!

Impact of The Language Property

You should be aware that the exact output of the Date/Time formatters depends on the language settings of the report.  Each report has got a property called Language.  By default this is set to en-US.  Let’s set it to nl-BE (yes, Belgian Dutch) and look at some examples:

Impact of Language property

Impact of System Settings

You should also be aware that this Language property is not the only property that has an impact on date/time value rendering.  The system settings play their role too and have an impact on how the Date/Time report parameters behave.

These are my current settings, Format is set to Dutch (Belgium):

Region and Language settings

With those settings here’s what a Date/Time report parameter looks like in BIDS preview:

A Date/Time parameter with system settings set to Dutch (Belgium)

As you can see, the month and day names or abbreviations are translated to Dutch and the format of the parameter is dd/MM/yyyy.

Here’s what that same parameter looks like when I change my system settings to English (United States):

The Date/Time parameter with system settings set to English (United States)

Conclusion

The above should give you sufficient info on getting those dates and times formatted anyway you’d like.  If not, type your problem down in a comment below and I’ll see if I can help you!

Have fun!

Valentino.

Share

Tags: , ,

« Older entries § Newer entries »

© 2008-2014 BI: Beer Intelligence? All Rights Reserved