Posts Tagged ‘SSIS’

SQLBits by the Sea a.k.a. Nerds on a Beach!

Who’d of thought it – it’s early April and I’ve come back from a SQL Server technical conference with sunburnt arms and a smile on my face.  Usually when I tell people that I’m going to a Microsoft SQL Server conference they sarcastically say things like “have fun”, or “rather you than me” and when I tell them that it also runs into Saturday and that I’m not even getting paid for it they’re even more incredulous.  I can understand the attitude since on paper it sounds like a rather dry affair but nobody that’s ever attended a SQLBits event would ever agree with that sentiment because it’s the strangest thing, somehow against all odds the SQLBits organisers have built an event crammed full of technical talks from some of the leading lights of the industry and they’ve also made it fun.  The craziest thing of all is that the Saturday ‘community day’ is also completely free and with over 40 talks it’s every bit as good as the the paid days, couple that with the prizes and other swag on offer from the sponsors and you can’t fail to walk away better-off than you went in!

Friday’s keynote was delivered by Microsoft’s Mark Souza and gave an insightful rundown of some new features coming in the next release of SQL Server codenamed Denali, there were some interesting High Availability and Disaster Recovery (HADR) features, a new Column Store styled index, the new data visualisation / dashboard tool Crescent as well as some additional enhancements that sound minor but will probably yield a large benefit (Windows Core support, Contained Databases, FileTable).  Also sharing the stage with Mark was David Flynn, CEO of Fusion-IO who gave a short but interesting overview of their ioDrive product which can provide amazing performance increases by moving IO bottleneck from the SAN onto NAND Flash directly attached to the PCI Express bus – though some nearby graffiti (pictured) showed that there’s still some loyalty to old storage media!

My top talks of the weekend included Jamie Thompson‘s session on SSIS Performance and Vincent Rainardi‘s talk on Advanced Dimensional Modelling but my ‘best of show’ goes to Martijn Evers’ talk on Data Vault – a data modelling technique that complements use of the well established Kimball and Inmon approaches to data warehousing.  I took a lot of useful hints, tips and tricks away from many of the talks I attended but the Data Vault talk gave me something much more valuable – an entirely new idea, something I’d never heard before but will make me think about data modelling in a slightly different way.  It’s the inclusion of these satellite topics (i.e. not directly related to SQL Server) that really makes SQLBits stand out and one of the reasons I keep coming back.  Aside from the talks my other highlights were sitting on the beach eating lunch and getting my first go on the Xbox 360 Kinect!

It’s worth mentioning that the entire event couldn’t happen without the sponsors and in particular I think a lot of good will should be shown to Microsoft.  Windows users tend to see them as a giant corporate entity that they never get to interact with but somehow it’s different in the SQL Server product team and they make a lot of effort drafting in some of their best people including Thomas Kejser, Ewan Fairweather, Lubor Kollar, Mark Souza, Connor Cunningham and Andrew Fryer (who completely schooled me on Kinect boxing).  Many of these guys fly in from the US and of course Microsoft can afford it but the point is that they don’t make a big deal out of it, these guys run sessions and wander around conference offering support to anyone that asks – where else do you get that?

There’s nothing quite like listening to a talk from someone who’s utterly engaged in what they do and wants to share the information with others and that’s what SQLBits is all about so if you’re a SQL Server developer or DBA you just have to go, it’s that simple but if even you’re a .NET developer or work with other database and BI products it’s well worth attending – for now I’ll look forward to seeing you all at the next SQLBits.

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Loading Data into MySQL with SSIS via ODBC (sql_mode = ANSI)

I was performing a test earlier, a proof-of-concept to show that we could use Integration Services to move data from one platform (PostgreSQL) to another (MySQL) without SQL Server itself being involved at all. 

Unfortunately I hit a slight snag on the first simple attempt, I could read the data without a hitch but despite multiple attempts at tweaking SSIS settings and reconfiguring connections I could not manage to get data into the MySQL target table.  Amongst other messages in the Output window in BIDS I could see the following error…

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”tblDestination” (“col1″, “col2″, “col3″, “col4″, “col5″, “c’ at line 1

After some Googling I determined that the issue was down to an incompatibility between the SQL being generated by SSIS and the default MySQL engine, specifically that MySQL was not setup to accept ANSI compliant SQL.  Most of the solutions out there seemed to require that the entire server was configured in the ANSI compliant mode which was a problem for me as it’s a shared server and I can’t go reconfiguring it at will.  Thankfully MySQL provides the ability to set the mode on a per connection level with the following command…

SET SESSION sql_mode= 'ansi';

The trick is – how to get SSIS to run this before inserting the data without messing with the package itself?  The easiest way is to have the ODBC driver send the command at the point SSIS opens the connection, this can be configured in the settings of the ODBC source…

This is a relatively common feature of ODBC drivers and allows you to setup initial configuration options for this exact reason, now I know that I will not impact any other users of the server and my SSIS package works just fine.

Trends in Business Intelligence & 2010 Review

It’s the time of year when magazine editors can’t resist the urge to fill their glossy wares full of ‘thing of the year’ articles, the print equivalent of the mid-season “clip show” that has plagued many a TV series.  Well, if it’s good enough for them it’s good enough for me so here’s my rather unstructured and unscientific take on Business Intelligence and Data Warehousing in the year that was – 2010…

Market Trends

To start, I’ve taken a series of snapshots from the excellent Google Trends showing global search volumes for each of the Big Four offerings to measure the level of interest.  It’s reasonably clear to see from the graph below that interest in OBIEE shows a small but steady growth whilst Reporting Services shows a marked decline and the other two offerings remains roughly static (maybe a small decline?), this surprised me given that with the release of 2008 R2 I think that Reporting Services is really getting to the point where it offers a legitimate choice in the BI marketplace.  Perhaps the issue that Microsoft have fragmented their BI offering to include a mixture of terms with Excel, PowerPivot, SharePoint, Analysis Services and Reporting Services all making up the BI stack and nobody really knows what to call it?

This year has also brought an increased emphasis on Mobile BI with the iPad and iPhone fast becoming common executive playthings, Business Objects making it’s Explorer and Xcelsius products available on Android in addition to the iPhone (Explorer only).  MicroStrategy took the mobile emphasis a step further (perhaps to help stick their head above the crowd) by announcing a strong focus on the mobile BI market and offering a free 25-seat licence for their Mobile Suite.  Despite a strong focus on marketing Mobile BI I’m still not convinced that any of the vendors have really hit the nail on the head with their solutions in that whilst many offer pretty visualisations and slick interfaces most seem to lack the kind of simplicity that helps to present information quickly and succinctly, even the frankly beautiful independent product RoamBI just feels a little overdone when it comes to actually using it.

Major Product Releases

It’s been quite a year in the BI & Database world with the launch of Microsoft SQL Server 2008 R2, Oracle Business Intelligence Enterprise Edition (OBIEE) 11g and IBM’s Cognos 10…

Microsoft’s launch is effectively a moderate evolution of SQL Server 2008 in most areas with little change to the database engine, it’s ETL tool Integration Services and it’s OLAP engine Analysis Services.  That said, R2 did bring some handy incremental features which will be especially welcomed by the budget-conscious with an increase in the DB size of the free Express Edition from 4GB to 10GB and the addition of Backup Compression to Standard Edition.  There were some interesting additions with PowerPivot, Master Data Services and StreamInsight thought I’m not sure that either will find a natural home for a good year or so as busy DBAs and developers struggle to find the time to try these new features out.

Despite the major jump in the version number Oracle’s release too seems to be mainly an evolution and as a great fan of the product I’m quite considerably relieved since Oracle could quite easily have been over-zealous in integrating their ‘own’ tools like Discoverer and Warehouse Builder with bought-in technologies like Siebel Analytics (which became the bedrock of OBIEE), Hyperion’s Essbase and Sunopsis (now Oracle Data Integrator).  One of the less exciting but fundamentally important additions is that the semantic layer employed in OBIEE will be directly and immediately compatible with future releases of other Oracle products in the CRM, ERP and Finance application spaces.

I’m not as familiar with Cognos as the other two tools having only experimented with Cognos 8 for a couple of weeks but from everything I’ve read it seems that Cognos 10 was certainly a major milestone in the product’s lifecycle.  Aside from the shiny sounding features such as Social Networking and iPad support (actually a very serviceable looking mobile BI app) there are some very cutting-edge additions to the product including a statistical engine drawn from SPSS and Active Reports which allows users to explore and analyse offline data including interactive email reports.

The Future?

No good review and roundup article ends without a nod to the future and whilst I’m not keen on making absolute predictions there are a few developments I’ll be keeping my eye on for 2011 and beyond.

The main event I’m anticipating is the release of Business Objects XI Release 4, I’ve not seen too many concrete details about functionality but over the last few years Business Objects have seen themselves distracted by the Crystal acquisition (including the shoe-horning of their core product into Crystal Enterprise) and in turn their acquisition by SAP.  As a regular and long-term user of Business Objects I’m really hoping that they’ll blow away some of the cobwebs and deliver some new functionality as well as rounding off some of the edges that in previous versions feel a little unfinished, it would be great too if they finally included the key functionality from the legacy desktop client (which many long-term customer still rely on) in their core Web Intelligence product (Freehand-SQL & Grouping – I’m looking at you).

Another area to watch in Business Intelligence and Data Warehousing as well as the wider enterprise market is cloud computing, Informatica’s ETL in the Cloud offering has seen improvements and adoption throughout 2010 and it’s almost a given that Microsoft will be adding some degree of ETL capability to their SQL Azure platform. I’d expect an announcement if not a release along these lines in the coming year, though it’s possible that ETL comes behind providing cloud based analytics (something SSIS guru Jamie Thomson suggests).

In a broader sense I’m expecting to see a little more interest and pickup in the open source BI market, I’ve been saying this for a while (“this time next year, Rodders…“) and I might be wrong for some time to come but I always keep an eye on companies using an Open Source model such as the ETL vendor Talend who recently acquired Sopera (a middleware and SOA vendor), BI vendor Jaspersoft and all-rounder Pentaho.  With the global economy still suffering a hangover from the sub-prime mortgage crisis and banking collapse people have been looking for cheaper alternatives and open source companies provide a great way to achieve that, though some of Talend’s high-end offerings are almost comparable in price with other commercial products.

Another possible area to watch out for is the area of Personal Intelligence, essentially Business Intelligence for the individual.  A colleague and I spoke about this back in 2008 and we could both see that as people increasingly become data-aware they’ll start to look inwards and aim to measure things about themselves, one obvious starting point is fitness and we already have sites to log and chart your weight and calorie intake as well as the brilliant Nike+ product that measures your pace, time and distance during a run using either a sensor in your shoe or GPS (iPhone app), see the sidebar of this blog or below (one of my runs on the Nike+ site) for examples of the output.

How to Edit DTS Packages in SQL Server 2008 R2

I’m on the verge of migrating a few DTS packages from a legacy application, there’s no point in re-writing them since the application will be phased out within the next 6-12 months so I figure that if I can migrate them ‘as is’ I’ll save myself some time and a few headaches.  SQL Server Management Studio (SSMS) provides a menu option under Management to Open, Import or Migrate DTS packages…

Initially if you try to use these features this you may receive the error:

“SQL Server 2000 DTS Designer Components are required to edit DTS Packages.  Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature.  (Microsoft.SqlServer.DtsObjectExplorerUI)”

To get around this you’ll need to follow a couple of steps:

1. Install the Microsoft SQL Server 2005 Backward Compatibility Components – you can get these as part of the Microsoft SQL Server 2008 Feature Pack (for some reason it’s not included in the 2008 R2 Feature Pack).  [x86] [x64] [ia64].
2. Install the Microsoft SQL Server 2000 DTS Designer Components – since DTS support was depracated in SQL Server 2008 you have to get these from the Microsoft SQL Server 2005 Feature Pack.  [SQLServer2005_DTS.msi]
3. Ensure that the PATH Environment Variable lists the SQL 2000 directory before the 2008 R2 one – I have to thank Ramoji Ryali’s Fundas for this solution, the correct version of sqlgui.dll will be used as long as:”C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\”
   is before:
   “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\”

After that, everything seemed to work fine – I did happen to log off and log back on again at some point so I’m not 100% sure if that was required or whether it was just coincidence – I definitely did not have to reboot though.

How to Enable 32-bit Runtime Mode in SSIS

If you’re just beginning to play with SSIS on a 64-bit box you’re pretty likely to hit a roadblock if your data sources have 32-bit ODBC drivers.  The error will become obvious immediately when you come to execute your package in BIDS (Business Intelligence Development Studio)…

The ODBC Driver Manager error reported in the Output window will be: ”The specified DSN contains an architecture mismatch between the Driver and Application”.  Thankfully this is a pretty easy one to sort out, all you have to do is…

1. Right-click on the Project name in the Solution Explorer and select Properties…

   

2. Choose “Debugging” in the left-hand pane…

   

3. Change the Run64BitRuntime value to False (by double-clicking on it). 

   

   For reference the help-text states that if the 64-bit SSIS runtime is not installed this setting is ignored so if you’re working on a 32-bit box in all liklihood you wouldn’t experience this issue. 

   

4. Click OK and re-run your package.