Assign value of variable based on value in another variable?

Question

Assign value of variable based on value in another variable?

peterzeke

SSCrazy Eights

Points: 9003
More actions
December 22, 2011 at 9:49 am

#391190

I have a variable that is assigned the name of a file in a For Each File loop: variable is called v_FileName. I need another variable to determine if the file is a filetype of ".xslx" -- here's the expression I'm using of a variable called v_Is_xlsx that doesn't seem to work, however:
FINDSTRING( @[User::v_FileName] ,".xlsx",1)
Perhaps some of you have had to deal with determining if a file is ".xls" vs ".xlsx" can assist?
Thanks in advance,
Pete

Viewing 6 posts - 1 through 5 (of 5 total)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

What doesn't work about that?

Just from face value, it looks like it should work fine. I've definitely used expressions based off variables that are set through ForEach loops, and never had any troubles with them.

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 2

It's as if the FINDSTRING function that is set to the expression of the variable just doesn't execute; there are no errors. When I set a breakpoint and watch the variables, I clearly see the v_FileName variable get assigned the full file path of the .xlsx file (it's the only file in the folder), but the variable v_Is_xlsx still shows 0 as if it didn't find "xlsx" in the string value of the v_FileName.

I'm not experienced with using variables and expressions as much as I'd like to be, so perhaps I'm making an incorrect assumption that the variable v_Is_xlsx would get a value assigned to it automatically in the same way that a computed column in a table exists dynamically.

Here's a link to a msdn thread I came across that I'm trying to follow (My variables have different names, though):

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e6c366e2-e4a7-4e38-9198-14c033142d03

A different approach I've come across using a script task appears to hold more promise for me, but I'd still like to know why my initial approach doesn't seem to work as expected. Here's the script task approach that later on discusses identifying a ".xls" file vs ".xlsx":

http://www.sqlservercentral.com/blogs/sqljoe/2011/03/21/ssis-foreach-file-enumerator-returns-more-files-than-expected-by-appending-a-wildcard-_28002A002900_-to-the-file-mask/

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Would it be possible to upload a copy of your .DTSX file so I could take a look at it?

I just tried it to double check, and it worked no problem. I had a ForEach loop that iterated through .xls files in a folder, and I had a variable which contained the file name of the files. I had another variable that was set as Evaluate as Expression = True, which just said FINDSTRING(@Variable, ".xls", 1). Then I put a script task in the ForEach loop, and used MsgBox to show the value of that variable, and it correctly showed the FINDSTRING location.

Are you sure you set Evaluate as Expression to true on your variable?

CHANDRASHEKHAR SINGH SSC Enthusiast Points: 164 More actions · Answer 4

Hi,

If the file is in the same folder, you can give the extension as *.* while configuring your Foreach Loop container.

Regards,

Chandrashekhar

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

CHANDRASHEKHAR SINGH (12/29/2011)
Hi,
If the file is in the same folder, you can give the extension as *.* while configuring your Foreach Loop container.
Regards,
Chandrashekhar

How does this help in determining whether the file type is xlsx?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel