I can understand that. I've wasted 3 days trying to figure this out. Finally got the output variables to work using ADO.NET as the Conn Type, but, as you said, it is FINICKY. I wanted to use the Data Flow Task to move data, but it appears that it doesn't like variables, either, so I'll take my result set and use OLE DB Command to get it into the destination database.

Thanks again.

SSIS lends itself to certain architectures. When I started working with the platform (in an early beta) I was often frustrated by "what I think it should do". Simply put: some things are not intuitive. I recommend attending training or SQL Server User Group meetings on the topic in lieu of training - and also books. There's some good books out there on SSIS. The Expert SSIS book from Wrox is my favorite new book.

:{> Andy

Andy Leonard

I got the Expert book a couple of months ago, and selectively read topics in it. On page 70, it says "Note that when you use parameterized queries, the statement cannot be parsed". That's the least of it, but all it says (of any use) about OLE DB parameterized source queries. I also have the Wrox Professional book, but neither mentions the issues. You are definitively right, it isn't intuitive at all.

I know this is an old post but I beat my head against this all day today until I discovered that I wasn't using my output parameter correctly. Make sure you don't SET the output parameter anywhere in the proc, only set it at the end when you are done processing. ex:

CREATE STORED PROCEDURE myProc @ID, @blnReturnValue bit OUTPUT

AS

DECLARE @myValue bit

SET @myValue = 0
WHILE
BEGIN
--conditional processing
SET @myValue = 1
END

SET @blnReturnValue = @myValue

GO