SQL Server example of datetime and datetime2

Mapping C# DateTime to SQL Server datetime2 via SSIS

With SQL Server 2008, Microsoft introduced the new, improved datetime2 format.  This newer time storage format is great, because it takes up less storage space, plus you have control over precision and can define your field to the exact specification required.  Database columns defined as datetime2 can be mapped in SSIS by using the DT_DBTIMESTAMP2 type.  However, in the scenario where you may have a Script Transformation in your SSIS package, and want to assign a .NET DateTime type to a Data Flow column that is mapped to a datetime2 field, you might encounter a DoesNotFitBufferException.

The reason for this is likely down to your specified field precision, and is easily fixed. Continue reading “Mapping C# DateTime to SQL Server datetime2 via SSIS” »

Case sensitivity in SSIS Lookup Transformation

Something that catches out a lot of new SSIS developers.  The caching mode used for a Lookup Transformation may affect case sensitivity.

The Full Cache option is case sensitive by default.  Partial and No-cache options use the Collation setting of the database (or table) to handle case.

Of course, using Partial or No-caching results in the SSIS package issuing a call to the database for each row in the data flow, so use them wisely!

Office 365 Team Site homepage

Power BI for Office 365 first thoughts

I’ve been meaning to write something on Power BI for a long time now, and I’m a little late in getting round to writing this, as most of the dust has already settled after Microsoft sent out the first round of invites to the Power BI for Office 365 preview, and a lot of people have produced some amazing work with Power BI.  Chris Webb has written a pretty comprehensive review on his blog, as have countless others.

What is Power BI?

For anyone living under a rock (or new to the world of MS BI), Power BI is a new offering from Microsoft which makes their new Excel-based self-service BI tools shareable and collaborative in a way that was previously only available for organisations rocking a SharePoint Enterprise installation.  By hooking their toolkit up to Office 365, they’re providing a cloud-based ecosystem in which to share, manage and explore data, using their suite of data tools: Power Query (formerly Data Explorer), Power Pivot (formerly PowerPivot), Power View, and Power Map (formerly GeoFlow).  If you want to know a bit more, I’ve got a more detailed post on the included functionality in Power BI for Office 365. Continue reading “Power BI for Office 365 first thoughts” »

SSIS OLE DB source using table or view access mode

Optimising SSIS to read from a view using OLE DB Source

I’ve really been neglecting the blog of late and have been taking a bit of a break from a lot of extra curricular business intelligence and data reading.  I figured it was about time to get back to posting though, and as luck would have it, my colleague Stephen came to me with an interesting SSIS performance issue that presented the perfect opportunity for a quick blog post. I’ve not written much about SSIS lately, having been drawn off by the shiny sparkle of developments in the self-service BI sector such as Power BI, and playing with Big Data tools like Hadoop.  But I still do a lot of work with SSIS and it’s still my go-to large scale ETL tool. Continue reading “Optimising SSIS to read from a view using OLE DB Source” »

Fix “login failed” error while editing Table Properties in SSAS Tabular via Visual Studio

Just a quick post regarding a strange problem I encountered while working on an SSAS Tabular model project.  Built on the same Vertipaq technology as Power Pivot, it’s very easy to get started and produce quick, efficient data models.  Unlike Power Pivot, which runs as an Excel add-in, SSAS Tabular models are developed in Visual Studio via the SQL Server Data Tools (SSDT) Business Intelligence add-on.  However, once you load up the development environment, it’s almost exactly the same, the only difference really being that when you build a tabular model in VS, it is developed against a temporary cube on a pre-installed SSAS Tabular instance.  Power Pivot just works entirely in memory, without requiring an SSAS instance.

Continue reading “Fix “login failed” error while editing Table Properties in SSAS Tabular via Visual Studio” »