Problem using expression in SSIS

Question

Post reply

Problem using expression in SSIS

Devesh_Srivastava

SSC Eights!

Points: 983
More actions
August 1, 2012 at 6:58 am

#392869

Hi,
Below is the expression i am trying to make work :
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))
Whe I use the exact string(Archive_Full_20120731200002) , I get the required result i.e Archive
But when I use a variable which contains th same string, the above expression gives the error that
Substring can allow -ve integer values( Here -2)
Please help me in this regard. I need to use a variable here and get it work.
Your help is appreciated!!!
Thanks
Devesh

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

Please post the expression which you tried - the one which gives the error.

Devesh_Srivastava SSC Eights! Points: 983 More actions · Answer 2

Below is the expression to be evaluated:

Substring (@Variable,1, (FINDSTRING( @Variable,"Full_", 1)-2))

@Variable is the variable containing the exact string 'Archive_Full_20120731200003'

But when i used this as a expression, it gives the error that substring does not allow -ve integer values.

Kindly help.

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 3

Substring cannot take negative values for the length argument.

Devesh_Srivastava SSC Eights! Points: 983 More actions · Answer 4

substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))

Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?

Phil Parkin SSC Guru Points: 247158 More actions · Answer 5

Works for me. I suspect that you may have got your variable syntax wrong. Here is my version:

Substring (@[User::TestString],1, (FINDSTRING(@[User::TestString] ,"Full_", 1)-2))

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 6

Devesh_Srivastava (8/1/2012)
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))
Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?

Good question. I only know that when attempting to put in a negative value for length in a much simpler expression, it errored on negative value of length parameter.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 7

herladygeekedness (8/2/2012)
Devesh_Srivastava (8/1/2012)
substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))
Then how come the above expression is working where the exact string is used instead of a variable and still negative value is accepted!!! Does it make sense ?
Good question. I only know that when attempting to put in a negative value for length in a much simpler expression, it errored on negative value of length parameter.

The result of

FINDSTRING( "Archive_Full_20120731200002","Full_", 1) -2

is 7. That's not a negative value where I come from

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 8

Not talking about the result, talking about the length argument.

I created a simple expression using -2 as length and was unable to save the expression due to "length cannot accept negative number" (okay, not a direct quote, but teh error was specific and when I changed length to positive number, no issues).

Phil Parkin SSC Guru Points: 247158 More actions · Answer 9

herladygeekedness (8/2/2012)
Not talking about the result, talking about the length argument.
I created a simple expression using -2 as length and was unable to save the expression due to "length cannot accept negative number" (okay, not a direct quote, but teh error was specific and when I changed length to positive number, no issues).

??

The expression I quoted was only the length argument - from the OP's example.

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 10

Phil Parkin (8/2/2012)
herladygeekedness (8/2/2012)
Not talking about the result, talking about the length argument.
I created a simple expression using -2 as length and was unable to save the expression due to "length cannot accept negative number" (okay, not a direct quote, but teh error was specific and when I changed length to positive number, no issues).
??
The expression I quoted was only the length argument - from the OP's example.

Wouldn't be the biggest shock to find that I am confused, but length is the last argument, and it is -2 to my eyes. I did not actually use OPs expression, I merely made my own very simple Substring on a string existing in my flow and attempted to use a neg number as length.

No worries, you got him fixed up and I have a giant batch of other fish to fry...

Phil Parkin SSC Guru Points: 247158 More actions · Answer 11

Wouldn't be the biggest shock to find that I am confused, but length is the last argument, and it is -2 to my eyes.

Just as a riposte to your fish-frying comment (you made me hungry while I'm at work! :-)), I'd like to sweep this one up.

Going back to the OP's example, if you look closely, you'll see that:

Arg1 = "Archive_Full_20120731200002"

Arg2 = 1

Arg3 = (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2)

mohanbabu.s Valued Member Points: 57 More actions · Answer 12

Phil Parkin - Thursday, August 2, 2012 11:06 AM
Wouldn't be the biggest shock to find that I am confused, but length is the last argument, and it is -2 to my eyes.
Just as a riposte to your fish-frying comment (you made me hungry while I'm at work! :-)), I'd like to sweep this one up.Going back to the OP's example, if you look closely, you'll see that:Arg1 = "Archive_Full_20120731200002"Arg2 = 1Arg3 = (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2)

I am encountering this issue even with correct expression. Its very strange, if I add +1 to result of findstring function its working fine.

SUBSTRING (@[User::var_filename],1, (FINDSTRING(@[User::var_filename] ,"_0", 1)-1))

Below is the error. Var_filename is file name enumerator from for each loop. If i pass value to this variable its working fine.

" The length -1 is not valid for the function substring. The length parameter cannot be negative. Change the length parameter to zero or positive value. "

Thom A SSC Guru Points: 99137 More actions · Answer 13

mohanbabu.s - Sunday, November 11, 2018 8:43 AM
Phil Parkin - Thursday, August 2, 2012 11:06 AM
Wouldn't be the biggest shock to find that I am confused, but length is the last argument, and it is -2 to my eyes.
Just as a riposte to your fish-frying comment (you made me hungry while I'm at work! :-)), I'd like to sweep this one up.Going back to the OP's example, if you look closely, you'll see that:Arg1 = "Archive_Full_20120731200002"Arg2 = 1Arg3 = (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2)
I am encountering this issue even with correct expression. Its very strange, if I add +1 to result of findstring function its working fine.
SUBSTRING (@[User::var_filename],1, (FINDSTRING(@[User::var_filename] ,"_0", 1)-1))
Below is the error. Var_filename is file name enumerator from for each loop. If i pass value to this variable its working fine.
" The length -1 is not valid for the function substring. The length parameter cannot be negative. Change the length parameter to zero or positive value. "

The error is telling you the solution here, however, the problem is the string doesn't contain the expression you're looking for, thus FindString returns 0. 0-1=-1; hence the error.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mohanbabu.s Valued Member Points: 57 More actions · Answer 14

I am getting this error when i try to evaluate the expression itself. Ideally it should give this error during run time right?

SUBSTRING (@[User::var_filename],1, (FINDSTRING(@[User::var_filename] ,"_0", 1)-1))

Above is the expression. @[User::var_filename] is the result of for each file enumerator. I even used the conditional expression check for non zero value from findstring function and only if its greater than zero executing above substring function. Something like below.

(FINDSTRING(@[User::var_filename],"_0", 1) > 0 ? SUBSTRING (@[User::var_filename],1, (FINDSTRING(@[User::var_filename] ,"_0", 1)-1)) : @[User::var_filename] .

For above expression, why is that i am getting error in the expression editor itself while evaluating it.

Kindly advice!!

Problem using expression in SSIS

Cookies on SQLServerCentral