developer.* Blogs - Inline Comments in SSIS OLE-DB Command SQL (And Other Tricks) - Comments

SSIS OLE Data Transformation with Output Columns

Tamim Sadikali — Wed, 10 Jan 2007 04:36:19 -0800

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

Vaibhav Gundapwar — Fri, 17 Nov 2006 06:39:06 -0800

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 OLE Data Transformation with Output Columns

Wayne Morrison — Wed, 30 Aug 2006 03:28:31 -0700

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

Wayne Morrison — Wed, 30 Aug 2006 02:51:21 -0700

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

Stored Procedure Arguments

Daniel Read — Mon, 03 Jul 2006 20:11:07 -0700

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

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

Bruce W Cassidy — Mon, 03 Jul 2006 17:14:50 -0700

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.

Professional SQL Server 2005 Integration Services

Paul — Mon, 06 Mar 2006 08:11:28 -0800

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!

Thanks, Paul

Daniel Read — Thu, 02 Mar 2006 11:20:47 -0800

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

SSIS & Inline comments

Paul Rayner — Thu, 02 Mar 2006 11:08:06 -0800

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.

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

Daniel Read — Thu, 02 Mar 2006 10:23:59 -0800

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: