I have colleagues that have been using this for 8 months or so, and I just took the dive into using it properly myself yesterday.

SQL Server 2005 Integration Services is part of the Business Intelligence set of SQL tools that replaced DTS packages from SQL Server 2000. It’s heart is in the right place, but it’s definately a V1 product.

I should have probably looked at the Adventure Works samples because it wasn’t easy to get into. However, once you figure out what all the bundled task and data flow tools do, you are ready to go…

Well almost:

• the designer interface is slow (even on an fast machine)
• error messages are all horribly truncated with the hover tool tips not being too helpful
• the debugging is pretty bad in terms of being able to really see what’s going on
• it’s possible to get items corrupted in which case you need to delete, reinsert and reconfigure

However, it is doing the job I want it to do – create a data warehouse from database, webservice and CSV sources using excel reference data – put it’s a little painful at times. Getting the security right to run one of these packages in SQL is also a fun configuration job. Of course, I can create custom tasks and data flow items if I want to which is a plus.

I admit to not yet having installed SQL Server 2005 SP2 and Visual Studio 2005 SP1, so I’ll give those a go.

I’m about to shift to Vista and I know SP2 is needed (plus a hot fix) to make scripted tasks work in the new OS.

I’m wondering whether they will replace the control takes with Windows Workflow Foundation at some point. I can see how needs could have arisen simultaneously but that either the fit or the timing wasn’t quite right…