Howard hi, You used long in the package but int in the SQL. Long vs. Int makes all the difference , the data types must match. More on this I could fine here: http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parametersArthur My Blog

Does your proc execute OK from SSMS? Looks good from here. You don't specify the SCHEMA in either the CREATE statement OR the EXEC statement in SSIS. Remember that this may execute under a different account than the one that created it. Try specifying the Schema: CREATE PROC dbo.GL_SP_Update_Log_Insert ... EXEC dbo.GL_SP_Update_Log_Insert ...Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks Arthur. That's not the issue. I had seen that post. You don't have Int32 as a choice when using OLE DB as the ConnectionType. However, SSIS is *very* picky about data, I do know that. It's not friendly at all like SQL on implicit data conversion. I figured it out. The issue was with the parameter name. I was tring to name the parameters explicitly in the ParameterMapping editor and the call. I changed the parametername to 0,1,2 for @ProcessFileName, @ProcessFileDate, @CurrentLogId respectively and changed the call to: EXEC GL_SP_Update_Log_Insert ?,?,? OUTPUT Worked when I did this. Howard

Hi All, Having an issue with an output parameter from a stored procedure. I've looked around and found a few posts on this subject and I am perplexed as to why it's not working. I'm pretty sure I have everything configured correctly. SQL2005 9.0.4294 build for SQL. Connection type of OLE DB. I am trying to assign it to an SSIS variable. When I call the proc from QueryAnalyzer, works fine. When I call from SSIS Execute SQL Task, I get the following error: [Execute SQL Task] Error: Executing the query "EXEC GL_SP_Update_Log_Insert @ProcessFileName = ?, @ProcessFileDate = ?, @CurrentLogId = ? OUTPUT" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. EXEC GL_SP_Update_Log_Insert @ProcessFileName = ?, @ProcessFileDate = ?, @CurrentLogId = ? OUTPUT You can see the above call. I know it's correct. I've also tried just using ?,?,? OUTPUT and the same thing. On configuring the task, I have ResultSet set to NONE. It's my understanding that you don't use a result set when using an OUTPUT parameter. I have the parameters mapped correctly, see below, taken from the ParameterMapping editor: VariableName, Direction, DataType, ParameterName, ParameterSize Formula::strExcelImportFile, Input, VarChar, @ProcessFileName, 0 Formula::strExcelImportDate, Input, Date, @ProcessFileDate, 1 Log::intCurrentLogId, Output, Long, @CurrentLogId, 2 Log::intCurrentLogId is type of Int32 and is the SSIS variable I'm trying to assign the value to. For completeness, I am including the stored proc, it's a simple insert: CREATE PROCEDURE GL_SP_Update_Log_Insert @ProcessFileName varchar(100), @ProcessFileDate datetime, @CurrentLogId int OUTPUT AS SET NOCOUNT ON SET @ProcessFileDate = CONVERT(VARCHAR(10), @ProcessFileDate, 101) SET @ProcessFileDate = CAST(@ProcessFileDate + ' 00:00:00.000' AS DATETIME) INSERT INTO Operations.dbo.GL_Update_Log ( ProcessFileName, ProcessFileDate, ProcessStatus ) VALUES ( @ProcessFileName, @ProcessFileDate, 1 ) SELECT @CurrentLogId = CAST(SCOPE_IDENTITY() AS INT) Thanks in advance. Howard

Just for argument sake, can you please set Result Set Property to 'Single Row' instead of 'NONE' and see if it works ??