ExecSQL Task Truncates Ouput Parameter

I am successfully passing several parameters into a SP using the ExecSQL Task. I have a single parameter that is an OUTPUT parameter in the SP.

I always recieve the first character of the OUTPUT string. I have tried chaning the data type of the parameter mapping [AnsiString, String, etc.] and I have tried changing the data type of the OUTPUT parameter [varchar, char, nvarchar, nchar]

In each case, only the frist character of the output value is returned and subsequently passed to the next SSIS component / task.

Any ideas why this value is truncated?It is a String.

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John|||

jloper wrote:

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John

Ignore that, I was wrong. Sorry!

Note to self: Check out your answers before posting them!

-Jamie

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql