April 2010

You are currently browsing the monthly archive for April 2010.

Just like last year, PASS will organize another 24 hours of PASS virtual conference.  If you’re not familiar with this, it’s 24 hours of free SQL Server-related training.  Each session takes one hour, making 24 sessions in total.

24 Hours of PASS: Celebrating SQL Server 2008 R2

I think the most difficult part of attending this event is combining it with your job/family.  Last year I was lucky: I happened to have a day off and at home we didn’t have anything big planned, so I was able to pick up a few sessions.  This year I’m not having any holidays in that period so I’ll need to filter quite well.  Or be lucky with the timing.  Which doesn’t seem to be the case: it’s a Wednesday and Wednesday means I go swimming with our oldest daughter in the evening.

So, hopefully the videos are available for download afterwards, then I’ll have something to watch on the train commuting to work.

The event will take place on May 19th, 2010, starting at 12:00 GMT (UTC).

Obviously, the focus will be on the newly released R2.  So what are you waiting for?  Go register before it’s sold out, it’s free!

What sessions am I interested in?

Session 05 (BI) – Start time 16:00 GMT
Implementing MDM Using SQL Server 2008 R2 Master Data Services
Presenter: Rushabh Mehta

Session 06 (DBA) – Start time 17:00 GMT
What’s Really Happening on Your Server? 15 Powerful SQL Server Dynamic Management Objects
Presenter:  Adam Machanic

Session 10 (DBA) – Start time 21:00 GMT
Using Data Compression with SQL Server 2008 and 2008 R2
Presenter: Maciej Pilecki

Session 11 (BI) – Start time 22:00 GMT
Easier than Ever Report Authoring in SSRS 2008 R2
Presenter: Jessica M. Moss

Session 12 (Dev) – Start time 23:00 GMT
High Performance Functions
Presenter: Simon Sabin

Session 15 (BI) – Start time 02:00 GMT
Producing Dashboards with PerformancePoint Services
Presenter: Peter Myers

Session 16 (BI) – Start time 03:00 GMT
Reporting Services Enhancements in SQL Server 2008
Presenter: Greg Low

Session 24 (DBA) – Start time: 11:00 GMT
BLITZ! 60 Minute Server Takeovers
Presenter: Brent Ozar

72 Hours Of PASS

Or better, that’s what the European PASS Conference stands for.  I went there last week and had a great time!  I met some friends over there, was able to put a face to some names which I already knew through the almighty internet, and had a chat with people I never met before.  Really good experience.

And that’s not all of course, I saw some really interesting sessions as well!  Let’s see, what sessions made me take out my notebook? (No, not to surf, the old paper version!)

There was of course the pre-conference by Donald Farmer.  He’s worth seeing just for his Scottish accent alone.  And on top of that, he’s one of the better speakers that I’ve ever seen.  And he’s “the BI guy from Microsoft” :-)   Initially I was planning to see the sessions by Adam Saxton but after seeing some memory dump analysis I decided that I would probably never do that myself anyway…  I think once you’re on that level, it’s time that you apply for a job at Microsoft.

So, what other sessions did I take notes at?  Chris Webb and his session about DAX.  Until now I haven’t had the time to play with PowerPivot, but I think time has come to get it installed.  He gave some quite interesting tips, such as the one on the Time table that shouldn’t end in the middle of the year.

Later on I saw Markus Raatz about the hidden BI treasures on CodePlex.  Yes Markus, if it were up to me you’ll be doing the sequel next year!

I also made some notes during another Chris Webb session, about warming SSAS cache.  A tip that I noted down: do not use subqueries in your MDX (such as the ones generated by the query designer in Reporting Services) because the global cache will not be used.

I saw several more speakers but won’t go into detail on those sessions.  Except for this last one: the session on PowerPivot (and everything that’s related to it) by Kasper de Jonge.  It was one of the last sessions on Friday, and we finally saw some interesting (read: not repeated for the n-th time) demos using PowerPivot.  Well done Kasper!  You were one of the lesser-experienced speakers (mind: I’m referring to speaking experience here, not technical!) and you were far better than some other speaker with years of experience (I won’t mention his name here but those who saw the presentation must know who I’m talking about).

That’s it for now people, have fun!

Valentino.

Share

Tags: ,

In a previous article I’ve shown you how to import data from an Excel sheet using the OPENROWSET() function.  And I concluded by stating that it’s not the best option when automating your data import.

Today I’ll repeat the Excel data import process by using SQL Server Integration Services, also known as SSIS.

I’ll be using SQL Server 2008 R2, but I’m quite sure that the process is very similar to the first release of 2008, and even to 2005.  The Excel file that I will be importing is the one used in my previous article, and I’ll also refer to some parts of that article, so you may want to have a read over that one when something here isn’t clear.

Furthermore I’m using a Windows 7 64-bit machine, with the ACE 14 driver (beta) installed.  To avoid any discussion about versions and for my own (future) reference, here’s the result of a SELECT @@VERSION:

Microsoft SQL Server 2008 R2 (CTP) – 10.50.1352.12 (X64)   Oct 30 2009 18:06:48   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Create SSIS Package To Import Excel Data

Usually you will start by creating a new package in an Integration Services project, add an Excel source to a new Data Flow, throw in some Data Flow Transformations and end your flow with an OLE DB Destination connecting to your SQL Server.

But that’s not the approach that I’ll take in this article.  I’ll make use of a shortcut (and meanwhile I’m showing you how well integrated some components really are).

First, I’m creating a new database called ExcelImport, using the Management Studio (aka SSMS).  Once the database is created, right-click on it and choose Tasks > Import Data.

Start the Import Data Wizard through Management Studio

This will open up the SQL Server Import And Export Data Wizard.  Like all good wizards, it starts with a Welcome screen containing an introductory text about its purpose – something about “create simple packages that import and export data between many popular data formats including databases, spreadsheets” – and so on.  It also includes a checkbox that says “Do not show this starting page again”.  That’s my favorite object on the page :-)   Okay, I agree, the page is useful for people who have never seen the wizard before and who may have opened it up by accident, but that’s about as far as its use goes methinks.

So, do whatever you like with the checkbox and click Next.  That opens up the Choose a Data Source screen.

SQL Server Import and Export Wizard - Choose a Data Source

In that screen you have several options in the Data Source dropdown.  The one we’re interested in is called Microsoft Excel.  Once that option is selected, the controls further down the screen change into what is shown in the screenshot.  Select your file and the right version, in my case I’ve got an Excel 2007 file.  If your sheet contains a header row, activate the First row has column names checkbox.

Clicking Next will open up the Choose a Destination screen.

SQL Server Import and Export Wizard - Choose a Destination

In that screen, select Microsoft OLE DB Provider for SQL Server as Destination.  Ensure that your SQL Server instance is the right one in the Server Name dropdown and the Authentication is filled out as expected.  The correct database should be selected by default because we did a right-click on it to start the wizard.

Another Next click opens up the Specify Table Copy or Query window.

SQL Server Import and Export Wizard - Specify Table Copy or Query

Here we can choose between either retrieving the full table – all rows, all columns – or writing a query ourselves.  I’ll go for the first option.

Click Next once more to open the Select Source Tables and Views screen.  That name seems a bit weird in the context of an Excel import but I guess that’s not really important here.  Just read it as “Select the sheet that you’d like to import”.

SQL Server Import and Export Wizard - Select Source Table and Views 

I’ll go for the ProductList$ Source.  The sheet in my Excel file is called ProductList.  Note that the Destination is editable – I’m changing the destination table to dbo.ProductList (with the dollar sign removed).

If you’d like to view your data right now you can hit the Preview button.  It opens up a window such as this one:

SQL Server Import and Export Wizard - Select Source Table and Views - Preview Data 

That should look familiar.

A more interesting button is the one called Edit Mappings.  Clicking that button opens a screen that lets you change the destination table’s schema.  By default, all columns that seem to be numeric are mapped to a type of float and all the others are mapped to nvarchar with a length of 255.  Depending on your situation you can either leave it as it is (in case you’re just loading a staging table and want to handle data conversions later on) or you should review each column (for instance when you’re erasing and loading the full table every night and this is the actual table being used by other processes).

Note: whenever a column contains a blank cell, its type will be nvarchar(255), even when all other values are numeric.  Also, if you don’t need Unicode support, don’t use nvarchar but change it to varchar instead.

Here’s the Column Mappings screen with some of the defaults changed:

The Column Mappings window

Everything that I changed has been indicated using the yellow marker.  I’ve changed some field types and lenghts, made one field non-nullable and adapted the destination name.

Clicking OK followed by Next brings us to the following screen, Review Data Type Mapping.

SQL Server Import and Export Wizard - Review Data Type Mapping

This screen gives another overview of all the columns that we’re planning to import.  Note that each column of which we’ve changed the type has gotten a nice yellow warning sign.  This happens because a data conversion needs to occur, and there’s always something that can go wrong when converting data.  The On Error and On Truncation columns show the action that should happen when such an event occurs.  We’ll leave them all at Use Global.  The Global settings are located at the bottom of the screen and are both set to Fail.  That’s the best option at the moment (the only other one is Ignore but that means you won’t get any notification in case of an error or truncation problem).

After clicking Next we end up at the Save and Run Package window.

SQL Server Import and Export Wizard - Save and Run Package

I have activated the Save SSIS Package checkbox and chose the Do not save sensitive data option.  By default it is saved in SQL Server, which is actually the MSDB.  That’s good because we’re going to examine the contents of the package later on so we want to keep it.

The Run immediately checkbox was activated by default.  This will execute the package in the last step of the wizard.

Another Next click and we’re on the Save SSIS Package screen.

SQL Server Import and Export Wizard - Save SSIS Package

Here we can give our package a decent name, such as ExcelImport.  You can also see the server on which I’m saving the package.

The final Next click brings us to the Complete the Wizard screen, woohoo!

 SQL Server Import and Export Wizard - Complete the Wizard

We can see a short overview of what we’ve configured in the previous steps.  Click Finish to execute and save the package!

SQL Server Import and Export Wizard - The execution was successful

And we’ve successfully executed the package!

Taking A Closer Look At The SSIS Package

When connecting to the Integration Services server (through SSMS for instance), I now have a new package in the root of the MSDB folder.

The ExcelImport SSIS package, stored in MSDB

It’s located there because I chose to save the package to SQL Server.

Adding An Existing Package To An SSIS Project

We’re now going to open it in the Business Intelligence Development Studio (aka BIDS).  So, open the BIDS and create a new SSIS Project (or use an existing one, doesn’t really matter).

Once the project is open, right-click the SSIS Packages folder in the Solution Explorer and select Add Existing Package.

Right-click SSIS Packages folder in Solution Explorer to Add Existing Package

That opens up the Add Copy of Existing Package window.

Sidenote: do you see that SSIS Import and Export Wizard option in the previous screenshot?  That’s right, the wizard that we’ve used extensively in the earlier part of this article can be launched from here as well.

Add Copy of Existing Package

Select SSIS Package Store as location of the package and enter the name of your server in the second dropdown.  Once that is done you can click the button with the ellipsis and select your package under the MSDB node.

Clicking OK will add the package to your project in the Solution Explorer.  Double-click it to open it up.

The Control Flow

In the Control Flow you can see two components: a SQL Task that contains a CREATE TABLE statement and a Data Flow.

Here’s what the CREATE TABLE statement looks like:

CREATE TABLE statement in the Execute SQL Task

As you can see, the table is created using the column names and types just like we configured them through the wizard.

Important to note here is that the Control Flow does not take anything else into account.  For instance, what happens if we execute the package twice?  It will fail because the table already exists!

In case you don’t believe me, just try it out!

Execute SQL Statement failed There is already an object named 'ProductList' in the database. 

The Data Flow

Opening up the Data Flow we see that it contains three components: an Excel source component that uses an Excel Connection Manager to connect to our now well-known Excel sheet, a Data Conversion Transformation to take care of the conversions that we requested and an OLE DB Destination that uses an OLE DB Connection Manager to connect to our SQL Server.

The Data Flow to transfer Excel data into SQL Server

Important to note here is that whenever an issue occurs, such as a conversion problem, the flow will fail.

In production environments, certain events need to be taken into account.  The purpose of this article was just to show you how you can use a wizard to generate an SSIS package for you.  You can now use this package as the basis for a well-developed Excel Import template.

Conclusion

With this article I hope to have shown you how to use Integration Services to import Excel data, and also that the Management Studio knows how to use other SQL Server components, such as SSIS, quite well.

If you’re running into some issues while using the wizard, or you just like reading what I write, check out my follow-up article covering some common pitfalls.

Need to go to sleep now, long drive tomorrow, PASS European Conference in Germany!  I do hope that Adam Saxton will be there because I was planning to see his presentations all day long.  I already read that one of the speakers – Brent Ozar – won’t be able to make it.  Darned ash cloud…  Next time someone starts talking to me about Azure I’ll run away screaming.

Just kidding :-)

Have fun!

Valentino.

References

Microsoft Support: How to import data from Excel to SQL Server

How to: Run the SQL Server Import and Export Wizard

Share

Tags: , , , ,

I usually don’t post about a Cumulative Update being released but this time I went over the list of fixes and noted some as being interesting to keep in mind.  So this post is more a "reminder to self" note than anything else.

Since a couple of weeks, CU7 for SQL Server 2008 SP1 is available for request on this Microsoft Support page.

Here’s the list of fixes that caught my attention:

978839 (http://support.microsoft.com/kb/978839/ )
FIX: A backup operation on a SQL Server 2008 database fails if you enable change tracking on this database

979777 (http://support.microsoft.com/kb/979777/ )
FIX: You experience some problems when you perform a grouping members operation on an Excel pivot table whose data source is an SSAS 2005 cube

976412 (http://support.microsoft.com/kb/976412/ )
FIX: An MDX query resets to a blank query when you click the Data tab in Business Intelligence Development Studio 2005 Report Designer

978930 (http://support.microsoft.com/kb/978930/ )
A parameter value is replaced by its default value when the parameter is hidden and in a snapshot report in SQL Server 2008 Reporting Services

979379 (http://support.microsoft.com/kb/979379/ )
FIX: Charts that are in a tablix of an SSRS 2008 report display incorrect data

979496 (http://support.microsoft.com/kb/979496/ )
FIX: Log entries are missing for the OnPreExecute event and the OnPostExecute event in SQL Server 2008 Integration Services

980925 (http://support.microsoft.com/kb/980925/ )
The result of a MDX query to query a calculated member on a attribute hierarchy lose the formatting you define on the calculated member in SQL Server 2008 Analysis Services

980949 (http://support.microsoft.com/kb/980949/ )
FIX: Header rows of a tablix do not always appear at the top of pages in an SSRS 2008 report

That’s it, have fun!

Share

Tags: , ,

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