Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem using expression in SSIS Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 1, 2014 4:26 AM
Points: 34, Visits: 240
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
Post #1338480
Posted Wednesday, August 1, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,100, Visits: 11,900
Please post the expression which you tried - the one which gives the error.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1338561
Posted Wednesday, August 1, 2012 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 1, 2014 4:26 AM
Points: 34, Visits: 240

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.
Post #1338681
Posted Wednesday, August 1, 2012 12:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
Substring cannot take negative values for the length argument.
Post #1338749
Posted Wednesday, August 1, 2012 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 1, 2014 4:26 AM
Points: 34, Visits: 240
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 ?
Post #1338936
Posted Thursday, August 2, 2012 12:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,100, Visits: 11,900
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))




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1338970
Posted Thursday, August 2, 2012 7:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
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.



Post #1339177
Posted Thursday, August 2, 2012 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,100, Visits: 11,900
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1339193
Posted Thursday, August 2, 2012 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
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).


Post #1339235
Posted Thursday, August 2, 2012 9:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,100, Visits: 11,900
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1339262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse