Inline Comments in SSIS OLE-DB Command SQL (And Other Tricks)

I discovered a nice SQL 2K5 Integration Services trick today with the OLE-DB Command transformation (and I would imagine the same thing works in other similar contexts). I'm referring to the ability to include a comment inside of the custom SQL statement used with an OLE-DB Command. This perhaps seems obvious, but it wasn't something that occurred to me at first. Here's what I mean:

INSERT INTO [Product]
           ([Name]
           ,[ProductNumber])
     VALUES
           (? --This is a SQL comment
           ,? --Notice the line break after the first comment)

As you're no-doubt aware, the double-hyphen is the single-line comment character for SQL/T-SQL (based on the ANSI standard, if I'm not mistaken). I started using this trick to quickly turn a SQL statement generated by Management Studio (Script Table As -> INSERT To -> Clipboard) in a parameterized statement for the OLE-DB command while also solving the problem of keeping up with the ordinal placement of all the ? characters:

INSERT INTO [UnitMeasure]
           ([UnitMeasureCode]
           ,[Name]
           ,[ModifiedDate])
     VALUES
           (
           ? --<UnitMeasureCode, nchar(3),>
           ,? --<Name, Name,>
           ,? --<ModifiedDate, datetime,>
           )

This makes the SQL statement a lot more maintainable in the OLE-DB Command editor, especially when there are a lot of columns. I use search-and-replace in my text editor to insert the ? characters at the beginning of the line. Then I go back through and replace ? characters with literals where appropriate, like so:

INSERT INTO [UnitMeasure]
           ([UnitMeasureCode]
           ,[Name]
           ,[ModifiedDate])
     VALUES
           (
           ? --<UnitMeasureCode, nchar(3),>
           ,? --<Name, Name,>
           ,getdate() --<ModifiedDate, datetime,>
           )

This is nice, but it's less than ideal because the comments don't show up when you're mapping input columns to these parameters. The parameters still show up as Parameter0, Parameter1, etc. on the column mapping tab. This forces me to keep a text editor open (damn these SSIS modal dialogs) with the same SQL statement so I can count along with the parameters as I map each parameter to a column, Alt-Tabbing back and forth between the column mapping and my text editor. Ugh.

One alleviation for this problem that I found is that if you instead do your SQL command as an EXEC call to a stored procedure, SSIS will give the parameters actual names based on the declared parameters of the stored procedure--no more Parameter0, Parameter1, etc. The price is that you have to go the extra trouble to write a stored procedure for your action.

Hope that helps. Thanks for reading.

Dan