A "spectrum_enable_enhanced_vpc_routing" parameter has appeared in Redshift.

Is AWS about to enable Redshift Spectrum with Enhanced VPC Routing?

AWS is knocking it out of the park at the moment with loads of new services and features coming out every week.  Indeed, it can be hard to keep up with the degree of change.  But, while working on one of our Redshift clusters today we spotted a potential scoop that would remove a key blocker for one extremely useful service, Redshift Spectrum.

Up until now it’s only been possible to use Spectrum if you don’t have Enhanced VPC Routing enabled on your Redshift cluster.  There are so many benefits to using Enhanced VPC Routing (reduced data transfer cost, control, security) that it’s hard to see why anyone wouldn’t be using it, especially if you move data between Redshift and S3 a lot.

But we spotted a new parameter being applied to one of our clusters when we made some maintenance changes to a parameter group.  There’s now a parameter named spectrum_enable_enhanced_vpc_routing showing, which hints that Amazon may be about to remove this crucial limitation.

 

Continue reading “Is AWS about to enable Redshift Spectrum with Enhanced VPC Routing?” »

Power BI iOS app

Impressions of Microsoft’s new-look Power BI

A couple of months ago, Microsoft’s new-look Power BI Preview rolled out globally.  Ditching the Office 365/Sharepoint Online requirement, the new Power BI is a streamlined, simplified version of the product that attempts to lose some of the bloat and give users a focussed, easy-to-use, self-service BI platform.

So has it worked?

Continue reading “Impressions of Microsoft’s new-look Power BI” »

Data Quality Services SSIS component error

Debugging Errors in SSIS Data Quality Services Cleansing Component

As part of Microsoft’s push to include business users in the Business Intelligence space, the addition of Data Quality Services to SQL Server’s feature set opened up the ETL process to the people who, arguably, know the data best.  Integration with SSIS was a great move, meaning that this user control extends to automated processes, further closing the gap between data and the business.

However, like most new components, Data Quality Services has some teething problems, and these can be quite hard to find when debugging your SSIS packages.  Here’s a quick tip that should help solve some of those tricky to find DQS issues. Continue reading “Debugging Errors in SSIS Data Quality Services Cleansing Component” »

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!