XML Source – Making things easier with XSLT

As I wrote in my previous post, complex XML documents will produce multiple outputs when you’re using the XML Source adapter. Most of the time it will be easier to pre-process your source file with XSLT to de-normalize it a bit. Reducing the number of outputs greatly simplifies your data flow. Let’s take the same…


Using XML Source

Providing a generic way to extract data from XML documents of any format is pretty complicated. The SSIS XML Source Adapter tries to do just that, to various degrees of success. XML Source is easiest to use when your input has a simple element/sub-element structure: <rootgoo> <goo> <subgoo>value</subgoo> <moregoo>1</moregoo> </goo> <goo> <subgoo>value</subgoo> <moregoo>2</moregoo> </goo> </rootgoo>…


SSIS Survey

The SQL User Experience (SQLUX) and Integration Services team are currently conducting a survey on ETL practices. We hope to use this feedback in our planning for the Katmai end game, and follow up releases. If you fill out the survey before December 15th, 2007, you’ll be entered in a draw to win one of…


Transfering a couple of tables at a time

I missed some of the comments to my post about creating your own Transfer SQL Objects Task with a script task, so I thought I’d post the answer here incase people miss my answer. You can use the ObjectList property of the Transfer object to specify which database objects you want to transfer. To transfer only certain tables…


Running packages from custom applications

There are a lot of different ways to run SSIS packages, and many situations call for a custom package launcher application. If you’re coding your own launcher, one thing to keep in mind is that certain SSIS functionality (such as the Script task / component) requires a multithreaded apartment (MTA), and can fail or behave…


Capture PRINT messages from a stored procedure

I recently helped with a customer issue where they had a long running stored procedure which output status messages periodically using PRINT statements. They wanted to capture these statements and output them into the SSIS log. Unfortunately, the Execute SQL Task doesn’t support this (it’s something we’re considering for the future), but it’s fairly easy…


Optional date parameter

This was part of the solution to the project my team was working on (that I’m still in the process of writing up), but I thought I’d post it separately because it has various uses. The Problem We have a package which does some aggregations based on a date to generate a report. Normally we’d want…


Data flow improvements in Katmai

As I previously mentioned, the current Katmai CTP contains a pretty major reworking of how data flow tasks get executed. Michael Entin has written up an overview of the changes that should be of interest to any long time SSIS users.


SQL Native Client 10 – provider name change

The provider name for SQL Native Client (SNAC) connections has changed in Katmai. Since this is the default provider for OLEDB connections, you’ll probably be affected by this change if you’re running SQL Server 2005 packages in Katmai. Version Provider name SQL Server 2005 (Yukon) SQLNCLI.1 SQL Server 2008 (Katmai) SQLNCLI10.1 When the full upgrade solution is…


Select variables dialog

A nice convenience feature in the new VSTA based script task is that we now provide a variable selection dialog for the ReadOnlyVariables and ReadWriteVariables properties. Ok, so there’s not much content in this post. I admit it – this was just a reason for me to play around with some of the plug-ins available for the Live…