Parameters in Execute SQL Task after SP2

Hi,

we have a Package, that worked fine for a long time. Now, it always fails on all computers with SP2 instaled. The problem apears in execute sql task, which uses ole db connection manager and calls a procedure with some input parameters (from sql server).

Package sends a undesirable data (".") into the procedure from variable, which has NULL or Empty string value. Originaly, data type of the Variable was Object, I tried to use also string, but in both cases the procedure was called with parameter "."

Please, can anybody tell me what's wrong?

Thanks

Janca

Do you have output parameters in your stored procedure?

In SP2 there was a change to the way stored procedures with output parameters are executed. Earlier there was no way to specify the parameter size and hence we were defaulting to the behavior of each provider when output parameters are involved. In SP2, you could specify the parameter size for the output paramters of variable length data types. Even this change should not break existing packages since the default value of the parameter is -1 should provider the pre-SP2 behavior.

If you can share the values of different properties on your execute sql task, we can take a look and see what is wrong with it?

|||I have a similar issue in SP2. I have a variable V1 (string) which I reference in the ParameterMapping of an Exec SQL Task. The Exec Sql Task uses an OLEDB connection manager to SqlServer 2005. In the parameter mapping I specify VARCHAR and parameter size = -1. The Sql Statement executes a stored proc with one INPUT parameter @.P1 VARCHAR(500). In the proc I test the length of @.P1. If I leave the SSIS variable V1 empty, when I exec the proc it says @.P1 is length = 1. I would expect the length to be zero. The value seems to be the ASCII NUL value (0). How can I get it to be an empty string as I would expect it to be? Thanks.