I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re ...

A few weeks ago I published a blog entitled Collecting information about your SSIS packages which demonstrated a way that you could use T-SQL and XQuery to derive information about your SSIS packages. Since then I’ve made a few amendments to the script that accompanied that blog post as follows: New columns The collected data now includes the ...

Did you know that is is possible to read the contents of a SSIS package (i.e. a .dtsx file) from within SQL Server Management Studio (SSMS) using T-SQL? For example, take the following T-SQL snippet: select cast(BulkColumn as XML)from openrowset(bulk 'C:\tmp\MyPkg.dtsx', single_blob) as pkgColumn; It uses OPENROWSET ...

While I was working on a package to incrementally load a dimension in the DWH I’m developing for a customer, I stumbled upon a tricky little bug (I think, I still have to have a response from MS) that had – and will have – a little impact on how I create my packages. I’ve found that, when working on an empty table that has an identity column, if ...

This is a follow-up to a posting from several months ago (Using XML Data Type Methods to query SSIS Packages). I included a temp table (based on information I found in the Microsoft.SqlServer.Dts.Runtime.VarTypeConvert class's constructor) which gives the conversion of the numeric data type value to the appropriate TypeCode (System.TypeCode). ...