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
Thanks, Paul
I appreciate your taking the time to comment, Paul. Always nice to receive feedback. I just checked out your blog; you've been posting some useful and interesting things yourself.
I see in your latest post you mention the new Wrox book Professional SQL Server 2005 Integration Services. I have not purchased this yet, but was planning to check it out. Have you been satisfied with the book? Does it go deeper than the basic info already available in MSDN/BOL?
Best,
Dan
Professional SQL Server 2005 Integration Services
Thanks Dan,
The book has been very helpful to me so far. Not so much because it is deeper than what is in MSDN/BOL, but because it is broader. What I mean is that every topic discussed has one or more associated examples explicating good techniques, and it is all laid out in a nicely progressive manner. Simple to complex, basic to advanced...culminating in a full case study example (which I have yet to work through in detail, but I have at least read through it and understood it). I usually learn best by seeing how others have done it and then imitating their techniques, so I have found it quite helpful. I wish it had been available back when I started using SSIS. My very first package used the Script Component and regex's, so I certainly dived in head-first.
Not only that, but there are some good tips I found along the way. For example, when I first started with SSIS I tried creating an Excel source and SQL Server destination, and kept getting errors related to not using unicode columns in my destination tables. This book explains why this is the case, and has some good advice on how to work around it.
I'm not sure that this book would teach you anything, since you seem to be a lot further along than me in most SSIS areas. But I suggest you browse through it at the local technical bookstore and see if it provides you any value. There is some redundant material in the book (seems to be symptomatic of the Wrox multi-author approach), but it is better than most. And the redundancy does allow you to pick up any chapter and know what is going on without having to refer back to previous chapters.
So, to sum up, I have found the BOL articles to be comprehensive and detailed, but the Wrox book is definately helpful in (i) fleshing out how the various parts of SSIS integrate, and (ii) giving good examples on how to accomplish this.
Cheers,
Paul
p.s. Thanks for checking out the blog. You may be the first intentional hit!
Inline Comments in SSIS OLE-DB Command SQL (And Other Tricks)
I ran into a similar issue -- that of safely binding parameters to lists where all you get back is Parameter_0, Parameter_1, etc.
To get around it, I created very simple stored procedures with correctly named/typed parameters. Now when I call the stored procedures, the names of the parameters are shown (rather than the Parameter_0 type of name.) Much safer!
So there's an alternative to using comments, and then having to use search/replace, etc, etc.
Stored Procedure Arguments
Thanks for mentioning this. After writing this post I subsequently discovered this fact and started creating stored procedures for all inserts and updates. I generally prefer to encapsulate queries in stored procedures anyway, but I didn't use them with SSIS at first.
Thanks again,
Dan
SSIS OLE Data Transformation with Output Columns
It is a useful transformation and I use it with stored procedures under the hood. However, I'm struggling to work out how to tie output columns of the procedure back to the transformation.
I used a Dereved Column transform to add an extra datetime column to my input schema. I then hope to map my string date and time field in as input columns and return a datetime. The proc works but the result is not returned.
Oh well, we'll see.
Cheers,
Wayne
SSIS OLE Data Transformation with Output Columns
Doh... you have to speciy in the OLE DB Command sql command field that the param is "output" (like you would when calling from another sproc). As in:
OLE DB Command Transformation editor:
Common Properties -> SqlCommand
sp_myproc ?, ?, ? output
Where the sproc api is:
create procedure sp_myproc
@pi_param1 varchar(10)
@pi_param2 varchar(10)
@po_param3 varchar(10) out
as
begin
...
end
Cheers,
Wayne
SSIS OLE Data Transformation with Output Columns
Aaarrrrggghh - this doesn't work for me!
I've created the sp & call/invoke it from within OLE DB Command exactly as specified, and whilst I can refresh + OK out of the transform without any error being raised, it falls down whilst validating the package. I get the following error:
'...component OLE DB Command failed validation and returned validation status VS_ISBROKEN...'
Any ideas???
Cheers,
Tamim Sadikali.
Stored Procedure Named Paramertes
How can I use named parameters passing for stored procs in OLE-DB Command transformation ?
For example, say I have stored proc
create proc LookupValue
@Key1 varchar(20) = NULL,
@Key2 varchar(20) = NULL,
@Value int OUTPUT
AS
BEGIN
---- Some logic to lookup value using supplied non null keys
END
I want to invoke this proc using named parametrs
EXEC LookupValue @Key2 = 'x'
EXEC LookupValue @Key1 = 'y'
EXEC LookupValue @Key2 = 'x', @Key1 = 'y'
Can I set OLE-DB Command transformation's sql command property to
EXEC LookupValue @Key1 = ?,@Key2 = ?
and pass parameters to it.
- Vaibhav
SSIS & Inline comments
Dan,
I enjoy reading your blog and have found it very helpful in my own SSIS adventures. Thanks very much. I have had major issues in the past using comments in TSQL in DTS, so it is good to know that SSIS is a lot smarter with this.
Cheers,
Paul.