BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9
This is the forum to discuss the Wrox book Professional SQL Server 2005 Integration Services by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy; ISBN: 9780764584350
You are currently viewing the BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
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.
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.
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
DECLARE @myValue bit
SET @myValue = 0
SET @myValue = 1