Where to go change ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where to go change ?

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 10:00:35

I get this error when i run an SSIS with a file system task(renaming files).
I dont know where to go check for it. Can anyone tell me?

Error occured with the following Error message: "The Expression for variable "FullArchivePath" failed evaluation. There was an error in the expression"
The length -1 is not valid for function "SUBSTRING". The length parameter can not be negative. change the length parameter to 0 or a positive value.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 10:03:50

Post the code used as the expression

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:06:26

you're probably giving an expression inside length part of SUBSTRING which under some conditions evaluates to negative number and hence the error. check the expression in length part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 10:12:36

This is someone else dtsx i am trying to debug... i am new.

i searched the dtsx using edit->find and replace->find in files and got
C:\SQL Programming\SSIS Jobs\FTP Transfers\QC Exports\Copy of FTP QC Exports.dtsx(119):
<DTS:Variable>

<DTS:Property DTS:Name="Expression">@[User::ArchivePath] + SUBSTRING( @[User::SourceFile] , 1 , FINDSTRING( @[User::SourceFile],"YYYY",1) -1 ) +(DT_STR, 4, 1252) Year( @[System::StartTime] ) + "_" + RIGHT("0" + (DT_STR, 2, 1252) Month( @[System::StartTime] ),2) +SUBSTRING( @[User::SourceFile] , FINDSTRING( @[User::SourceFile],"." ,1) ,4)
</DTS:Property>
<DTS:Property DTS:Name="EvaluateAsExpression">-1</DTS:Property>
<DTS:Property DTS:Name="Namespace">User</DTS:Property>
<DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
<DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>

<DTS:VariableValue DTS:DataType="8">E:\Client Extracts\QC Exports\abc_2009_10.txt</DTS:VariableValue>

<DTS:Property DTS:Name="ObjectName">FullArchivePath</DTS:Property>
<DTS:Property DTS:Name="DTSID">{2D7E98A4-7D28-4102-B012-BAF40DD978FE}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName"></DTS:Property>
</DTS:Variable>

I dont know where should i go see these expressions and variables set

i am not sure why it is giving me a problem after working for few years.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:18:04

this will break in cases where you dont have pattern YYYY coming inside your @[User::SourceFile] as then FINDSTRING( @[User::SourceFile],"YYYY",1) will be 0 and you get 0-1=-1 as length for SUBSTRING, so if you've values without this pattern you need to handle them separately and do substring only for cases where you've pattern available

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 10:20:36

you are correct.. it worked when i removed the file which violated that substring function.
ok.. where do i go change this ? i am searching intoall expressions everythign is empty in the dtsx? where is this setting?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:24:55

its inside evaluation part for variable FullArchivePath I guess.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 10:27:07

sorry i am too new...
where do you define variables in the dtsx. how do i go to its definition?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:33:46

click on View->Other Windows-> variables from top menu and it will pop up a window with all variables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 11:10:07

Thanks

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 11:20:14

how to i check for the condition and do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 12:21:47

what condition? whats your actual requirement? can you state that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 12:43:31

if the file name has a YYYY in that replace it with the Year and MM if exists replace with month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 12:44:45

where do you get year and month values from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 13:02:28

DTS:Property DTS:Name="Expression">@[User::ArchivePath] + SUBSTRING( @[User::SourceFile] , 1 , FINDSTRING( @[User::SourceFile],"YYYY",1) -1 ) +(DT_STR, 4, 1252) Year( @[System::StartTime] ) + "_" + RIGHT("0" + (DT_STR, 2, 1252) Month( @[System::StartTime] ),2) +SUBSTRING( @[User::SourceFile] , FINDSTRING( @[User::SourceFile],"." ,1) ,4)
</DTS:Property>

@[System::StartTime].... but how do i test a condition here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:04:04

so what do you want to do cases where you dont have YYYY and MM patterns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 13:26:07

just leave it unchanged

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:41:35

try like


...
DTS:Property DTS:Name="Expression">@[User::ArchivePath] + (FINDSTRING( @[User::SourceFile],"YYYY",1)>0 ? SUBSTRING( @[User::SourceFile] , 1 , FINDSTRING( @[User::SourceFile],"YYYY",1) -1 ) +(DT_STR, 4, 1252) Year( @[System::StartTime] ) + "_" + RIGHT("0" + (DT_STR, 2, 1252) Month( @[System::StartTime] ),2) +SUBSTRING( @[User::SourceFile] , FINDSTRING( @[User::SourceFile],"." ,1) ,4):  @[User::SourceFile])
</DTS:Property>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 13:42:23

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 23:55:45

did it work?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources