Hi, I have a question on how to grab a value from a stored procedure in an execute sql task and then make a decision on that value. My SP is this: CREATE PROCEDURE [dbo].[bnh_CheckForErrorFileRecords] @NewFileName varchar(500), @ReturnStatus varchar(50) OUTPUT I created an execute SQL task and now I want to grab that '@ReturnStatus' and make a decision. I currently have an ADO.net connection set up in my Execute SQL Task. I have the following as paramaters: Variable Name - User::ProcessedFileName, Direction - Input, Data Type - String, Paramater Name - @NewFileName, Paramater Size - 500. Variable Name - User::ErrorRecStatus, Direction - Output, Data Type - String, Paramater Name - @Return Status, Paramater Size - 50. Now, I want to make a decision based off of this User::ErrorRecStatus, so I've created an expression flow. The expression is @[User::ErrorRecStatus] == "99" I know that this status is coming from the database, but my flow is not going this way. Can anybody help me? All my variables are strings and I've made the stored procedure return a varchar. Thanks, Phil

i Phil, you can use Precedense Contraints to assess the value of the variable and redirect the flow. one simple example: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspx?wa=wsignin1.0 with the expression: http://ericwisdahl.wordpress.com/2009/02/18/ssis-configurations-expressions-and-constraints/ and a more close to what you need: http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/ Arthur My Blog

Hello, Not sure what you have in your SP, If you have only one input variable as input and one output then why you have two variables added as output. Name - User::ErrorRecStatus, Direction - Output, Data Type - String, Paramater Name - @Return Status, Paramater Size - 50.????????????? It might be easy for you , create a procedure that takes input variable and then return your Id , You can use Select Statment to do that, in that case you don't have to worry about mapping output parameters, you can select single row result set and go to result set and get your value in any variable. http://www.sqlis.com/post/The-Execute-SQL-Task.aspx ThanksAamir

Hi, Use Expression in the precedence constraint @[User::ErrorRecStatus] == "99" Make sure your precedence constraint is in blue color it will eveluate based on the expression. Thanks, Shobhit

Aamir - I thought about that, but was having some difficulty setting the 'result set' to the value of the returning set. Can you elaborate a bit on the variable types? I know SSIS is really particular about what's coming back. If I have a varchar coming back, then what should the variable be? I'll work on changing my stored procedure to not have an output paramater; instead returning a value in a select clause. Thanks, Phil

It's still not getting to the @[User::ErrorRecStatus] == "99" presedence constraint even after I changed the SP to return a value instead of output paramater. I made the result set result name - 0. I mapped that to my variable @[User::ErrorRecStatus]. The variable is a string. Here's my SP: CREATE PROCEDURE [dbo].[bnh_CheckForErrorFileRecords] @NewFileName varchar(500) AS SET NOCOUNT ON Declare @returnStatus varchar(50) /* exec bnh_CheckForErrorFileRecords @NewFileName = 'BulkNewHire_20110131_BulkNewHireFileTemplate.xlsx' */ if @NewFileName is Null OR @NewFileName = '' Begin select @ReturnStatus = '-1' GOTO ReturnLogic End if @NewFileName = 'DO_NOT_DELETE_ME.xlsx' Begin select @ReturnStatus = '1' GOTO ReturnLogic End if not exists ( select * from BulkNewHireUploadedFile where [FileName] = @NewFileName ) Begin --select 'BulkNewHireUploadedFile FileName not found' select @ReturnStatus = '-2' GOTO ReturnLogic End if exists ( select * from BulkNewHireErrorLog a inner join BulkNewHireStagingData_TEMP b on b.BulkNewHireStagingData_TEMPID = a.BulkNewHireStagingData_TEMPID where EmailedErrorFile = 0 ) Begin --select 'Error recs found' select @ReturnStatus = '99' GOTO ReturnLogic End else Begin --select 'Error recs found' select @ReturnStatus = '5' GOTO ReturnLogic End ReturnLogic: Begin if @ReturnStatus is not null Begin SELECT 'ReturnStatus' = @ReturnStatus End End

I got it figured out. After closely looking at my SP, there was something messed up in there. Looks like I'll adjust my other workflow to use ADO.net, return varchar and have the variables set up as strings. This seems best approach. Thanks all!