AMO in SSIS Script Tasks

Lately there has been some good stuff on using AMO in SSIS script tasks for processing SSAS objects posted by Vidas Matelis with some helpful input from Darren Gosbell.

Not to be outdone, Adrian Downes has just posted part two of a series detailing how to use AMO in SSIS script tasks to dynamically create partitions and then create aggregations on said partitions.

BI Documenter update - SSRS now supported

A new version of BI Documenter (v 1.6.1) has just been released, and now supports documentation of SSRS along with SSIS and SSAS. Read more about the other improvements & enhancements in this new version on their blog.

At last, a SQL 2005 BI documentation tool!

Since the SQL 2005 release last November we've been hard up to find any doco tools that support SSAS, SSIS & SSRS. There are plenty of RDBMS doco tools but not much (anything?) in the way of a SQL BI tool. Ever since Chris Webb made us aware of BI Documenter in June my colleagues at SDM and I have been working with the developers of this product as we really liked the direction they were headed. We hope that in providing heaps of feedback & ideas we can get a tool that does what we really want. The latest release is well on the way to what we are looking for and supports SSAS and SSIS along with the usual relational DB stuff. Check it out here.

They tell us that future plans for the product include SSRS, data lineage/dependency mapping capabilities and SSIS diagrams. As it stands they have lots of underutilised meta data and are continuing to look for useful things to do with it.

Does anyone know of any other SQL 2005 BI doco tools out there?

SSIS - Best Practices a la Jamie Thomson

If you don't already subscribe to Jamie Thomson's brilliant SSIS blog, you should. There is so much good stuff that Jamie has posted in the last 18 months; it's an SSIS reference site unto itself.

His latest on Best Practices and Package structure summarise many of his other posts on getting the most out of SSIS as well as referencing other useful articles & posts by the likes of Kirk Haselden and the SQLIS.com team. These two posts serve as a great bird's eye view of his experience so far with the product.

Do yourself a favour and read both posts, twice.

SQL 2005 BI - Logging, Reporting and Metadata

Just as an addition to Chris Webb's post regarding the Metadata Samples Toolkit (I had a play around with the contents of the toolkit this past Sunday - well worth it)

Another download that is worth spending time looking at is the sample SSIS Logging Reports - blogged about by Kirk Hazelden a few days ago.

There is a lot of good, thought provoking stuff in both these sample packages. Combining the techniques used in both of them along with a few best practices from Project REAL could take a BI Consultant a long way to setting up a common metadata framework which is easily set up & deployed as standard for every new SQL 2005 BI project.

SSIS - Konesans Components

Update: I received a comment from Darren Green (who I assume is from Konesans?) on a previous post about the cost of the Konesans components.

Darren said: "The Konesans components currently released are all free, and will stay that way. Some future components will be paid for, but they will be clearly marked as such."

Good news!

SSIS - Performance Best Practices

Ashvini Sharma has posted a useful list of best practices on his MSDN Blog.

FYI, Ashvini also has an SQL Junkies blog too.

Gotta love SSIS!

I cannot say enough good things about SSIS. What a fantastic addition to the SQL Server "Services" family this is. For those who have been living under a rock for the last year or two SSIS (SQL Server Integration Services) is the replacement for DTS (Data Transformation Services).

There are so many good blogs out there which are dedicated to this subject. Conchango's Jamie Thomson has a fantastic blog which is pretty-much dedicated to all things SSIS. Another one I subscribe to is that of Simon Sabin. There are plenty more good ones out there - I'll gradually add them to my blogroll.

The achitecture of SSIS is already giving rise to all sorts of innovative and helpful custom transforms written by various experts.

- Simon Sabin is working on a few
- Konesans have develped several already which are available to play with (I know that Jamie is a big fan of the Trash Destination adapter - great for development)

Of course I am not sure whether there will be a cost associated with these components or not. Just the same this early proliferation of custom transforms underscores how extensible the SSIS architecture is. There is a great webcast by Ashvini Sharma on extending SSIS with custom components, well worth watching.