The trouble with data…

Scenario: Your SSIS package fails and you see error messages in your log files along the lines of

Description: Failed to retrieve long data for column “xxxxx”.

End Error  Error: 2010-03-03 16:30:02.84     Code: 0xC020901C     Source: Data Flow Task 1 Source – Query [1]

Description: There was an error with output “OLE DB Source Output” (11) on component “Source – Query” (1).

The column status returned was: “DBSTATUS_UNAVAILABLE“.

You will probably find that the column “xxxxx” is something like a text or picture data type (i.e. BLOB or similar) and what’s happening is that SQL Server is either running out of  Disk Space to store this stuff.

Step one is obviously to check if you actually need all this data and optimise your dataflow by removing any unnecessary columns.

If you can’t do that, try setting the BLOBTempStoragePath and BufferTempStoragePath to Fast Drives, or at least drives with more disk space.

See these posts for more details

http://connect.microsoft.com/SQLServer/feedback/details/346675/ssis-runs-out-of-temp-file-names

http://blogs.msdn.com/sqlperf/archive/2007/05/01/set-blobtempstoragepath-and-buffertempstoragepath-to-fast-drives.aspx

Here’s a link to a useful article by Kristen Hodges about using the new 2008 MERGE SQL construct to carry out Type 1 & Type 2 Dimension updates

Article

Based on our experience, this is certainly worth a look over the old SCD component – we have seen some major performance improvements using MERGE over the SCD components (and other workarounds that we had previously used).

SQL Server Kilimanjaro and Project Madison

This week the BI Conference has kicked off with an announcement!  There will be a BI focused release of SQL Server code named Kilimanjaro which is expected H1 2010 with CTPs available within the next 12 months.  Kilimanjaro is focused on "People Ready BI".  
A key component of Kilimanjaro is "Project Gemini", this is all about self-service BI using Excel.  The vision behind Gemini is to free IT resources by giving business intelligence analysis and reporting capabilities to the end-users without IT losing control.

Is this where Microsoft finally fill the gap left when they shoe-horned some parts of Proclarity in to PPS, leaving themselves without a great stand-alone OLAP client tool? Let’s hope so… I am a little uncertain as to the direction that they are taking BI in. As a once frustrated end-user, I can see how the masses of information workers will love the capabilities. I can also see that this may well lead to a huge increase in unstructured an potentially conflicting information if not managed very carefully.

At the same Microsoft will be releasing "Project Madison", this is the integration of Datallegro which will offer you an easy scale out solution to support data warehouses of up to 100s of terabytes.

All in all very exciting news and I will keep you up-to-date whenever I learn more. I am certainly hoping for more information on the management of the data, how this fits with the overall DW framework, and mysterious lack of information to date on PerformancePoint2…

Although SQL Server 2008 was “launched” last February at a marketing event that also featured Windows Server 2008 and Visual Studio 2008, this week Microsoft finally released its database software to manufacturing.

SQL Server 2008 is available to MSDN and TechNet subscribers and will also be available for evaluation downloads. Pricing for the database will be the same as the previous version, SQL Server 2005.

http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx Has Ent, Dev, Std, Web and Workgroup for subscriber downloads, can’t see Express or Ent Trial yet on the non subscriber pages.

Also Eval Edition and Feature Pack are now available

If you need some help installing, see this SQL School Video

Great set of articles over at Tod McKenna’s blog going in to a high level of detail on Kimball’s 34 subsystems of the ETL. So far, Todd has reached number 15 of 34. I liked his take on Surrogate Keys, it’s a topic worth investigating if you are one of the people who still try to forge ahead using natural keys only to find issues later on….

When integrating data into a Dimensional Model, you need a mechanism to assign new primary keys to each dimension. These primary keys will be used in your Fact table as foreign keys. You cannot use natural keys because they are likely to repeat — this is especially true if you are maintaining history using SCD Type 2 (more on this in a bit). Subsystem 10 addresses this important need, by specifying the need to generate surrogate keys for all dimensions.

On general release for around a week now, which suggests that RTM is not far away

• Download SQL Server 2008 here
• Download the updated feature pack here
• Download updated sample databases here 

Dan has blogged a nice little walk-through of his installation experience with the final CTP of SQL Server 2008. 

Dan English’s BI Blog: SQL Server 2008 Feb CTP6 Installation Experience