March 6, 2008

Reason #94,532 to Hate SSIS

by Brian Donahue

I've mentioned before my fuming hatred for SQL Server Integration Services.  And I'm not the only one. 

SQL Server 2005 gives us a fairly simple way to convert tabular data to XML using FOR XMLBroken Link: http://msdn2.microsoft.com/en-us/library/ms178107.aspx.  In the query analyzer, you can actually click on the results and it will open up as a file which you can "Save as..."

So, wouldn't you think it should just be a cakewalk to export the results of a FOR XML query to a file as part of an SSIS package? 

WRONG!

There is no built in data flow component or data destination that will take the results of a FOR XML query and export to file.   In fact, there is NO straightforward way to do this at all, aside from manually via Query Analyzer.  I've found some info about custom script tasks, and using command line (SQLCMD or OSQL) but none are pretty. 

In fact, I tried the custom script task approach, and it worked fine locally, but when I tried it on my production system, there was some unicode issue with invalid characters and it wouldn't save.  Leaving me in an impossible situation, as I'm not SQL Server guru.

This is really just an absolutely inexcusable oversight for an "Enterprise" worthy database solution to make this so difficult. 

My future ETL processes for clients will most certainly use Rhino.ETLBroken Link: http://www.ayende.com/Blog/archive/2007/07/24/Rhino-ETL-First-Code-Drop.aspx if at all possible, before looking at SSIS.  SSIS remains one of the worst tools I've ever experienced.

UPDATE:  I got this working late last night by changing the SQL from a straight query to a stored procedure and using an ADO.NET connection rather than an OLE DB connection.  I think the latter change was the biggest factors.  I have had other issues with OLE DB connections in SSIS.  I don't really understand, but in SSIS you often need to choose one connection over the other.  For example, OLE DB connections can not execute Stored Procedures, but ADO.NET connections can.  I still hate SSIS.