asked on

substring in a global variable

i'm looping through a whole bunch of files in a dts pacakge. i'm attempting to loop through them and load them into a table. one of the columns is actually based on the filename. can i substring the global variable in the activeX such that it is ONLY everything to the left of the first underscore in the filename, instead of the whole filename?

example: VFH_ETF_Composition.csv

my gv is gv_FileFullName, which for whatever reason is equating to this right now: \\servername\shared\aaaa\Compositions\VFH_ETF_Co

I need to substring the gv such that filename becomes this: VFH_basketid

but, i don't want that servername\directory stuff. i just want the filename.
and, If i could just pull the 'VFH', couldn't i prepend everything to '_basketid' ?
how would I do that?

any thoughts?

rmacfadyen

The scripting runtime has a filesystemobject that provides a method for getting just the file name. So a bit of vbscript code like:

dim fso
dim Filename
set fso = Server.CreateObject("Scripting.FileSystemObject")
Filename = fso.GetBaseName(s) & "." & fso.GetExtensionName(s)

This handles shares etc etc etc.

Another approach is substring, reverse and charindex:
substring(@s, (len(@s) - charindex('\', reverse(@s)) + 2), (charindex('_', @s, len(@s) - charindex('\', reverse(@s)) + 2) - (len(@s) - charindex('\', reverse(@s)) + 2)))

Though that's a bit of a brutal string expression to figure out what's going on with (i.e. debugging or changing will be nasty).

Regards,

Rob

dbaSQL

ASKER

i'm not sure i understand, rob. filesystemobject will get me just the filename, but how do i then substring it down to just 'VFH' ? or, better said, once i've filesystemobject'ed down to only the filename, how do i strip everything from the first underscore over to the right?

rmacfadyen

oops.

dim fso
dim Filename
set fso = Server.CreateObject("Scripting.FileSystemObject")
Filename = left(fso.GetBaseName(s), instr(1, fso.GetBaseName(s), "_") - 1)

Sorry about that... a Doh! moment.

Rob

dbaSQL

ASKER

no worries, rob, i'm just trying to follow you on this. i really should be done, but....

i'm doing this:

' Copy each source column to the destination column
Function Main()
DTSDestination("BasketID")=DTSGlobalVariables("gv_FileFullName").Value
Main = DTSTransformStat_OK
End Function

mind you, i am not looping at all, so that is possibly totally wrong.
but, how do i revise that with what you've suggested? i tried and it failed to invoke dts step 1, indicating 'server is invalid/?

dbaSQL

ASKER

fails w/this error:

'The number of failing rows exceeds the maximum specified.
Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description: Invalid procedure call or argument: 'left'

Error on Line 13

This is what I tried:

Function Main()

dim fso
dim Filename

set fso = CREATEOBJECT("Scripting.FileSystemObject")
set Filename = left(fso.GetBaseName(s), instr(1, fso.GetBaseName(s), "_") - 1)

Main = DTSTransformStat_OK
End Function

rmacfadyen

Ah... looks like some of your filenames may not have underscores in them:

Function Main()
dim fso
dim Filename
dim UnderscorePosition

set fso = CREATEOBJECT("Scripting.FileSystemObject")
UnderscorePosition = instr(1, fso.GetBaseName(s), "_")
if UnderScorePosition <> 0 then
Filename = fso.GetBaseName(s)
else
Filename = left(fso.GetBaseName(s), instr(1, fso.GetBaseName(s), "_") - 1)
end if

Main = DTSTransformStat_OK
End Function

Note: The MAIN function does not actually return the Filename (it is returning DTSTransformStat_OK). Maybe it should be assigned to DST global (or some such):

DTSGlobalVariables("GlobalVarName") = Filename

Regards,

Rob

dbaSQL

ASKER

same exact error, except for now it's Error on Line 18
i really need to get around this.

rmacfadyen

Can you post the code you're using?

Also I just noticed the code I provided (intended as an example of how to do this... not necesarily the full complete solution) does not acquire the filename from DTS. The code fragment "fso.GetBaseName(s)" illustrates this... "s" should be replaced with the actual filename.

Regards,

Rob

dbaSQL

ASKER

surely that's one of my errors then....possiby my LEFT, too
my gv is gv_FileFullName, here is the activeX, what do yiou think?

' Copy each source column to the destination column
' DTSDestination("BasketID")=DTSGlobalVariables("gv_FileFullName").Value

Function Main()
dim fso
dim Filename
dim UnderscorePosition

set fso = CREATEOBJECT("Scripting.FileSystemObject")
UnderscorePosition = instr(1, fso.GetBaseName(s), "_")
if UnderScorePosition <> 0 then
Filename = fso.GetBaseName(s)
else
Filename = left(fso.GetBaseName(s), instr(1, fso.GetBaseName(s), "_") - 1)
end if

Main = DTSTransformStat_OK
End Function

ASKER CERTIFIED SOLUTION

rmacfadyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbaSQL

ASKER

The pkg ran now, except it didn't loop to the other files. It only inserted one file and the genID column is NULL for every one of them.
'genID' is the field that we're trying to insert the filename into, after it's been revised from this: VFH_ETF_Composition.csv
to this: VFH_basket.

(pulling off everything from the 1st underscore over, and prepending with '_basket' )
That's the filename insertion we're trying to do. i'm not sure why the loop isn't working.

Step 'DTSStep_DTSActiveScriptTask_1' succeeded
Step Execution Started: 5/31/2006 1:05:54 PM
Step Execution Completed: 5/31/2006 1:05:54 PM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_2' was not executed

Step 'DTSStep_DTSActiveScriptTask_3' succeeded
Step Execution Started: 5/31/2006 1:05:57 PM
Step Execution Completed: 5/31/2006 1:05:57 PM
Total Step Execution Time: 0 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_1' was not executed
Step 'DTSStep_DTSActiveScriptTask_4' was not executed
Step 'DTSStep_DTSActiveScriptTask_5' was not executed
Step 'DTSStep_DTSActiveScriptTask_6' was not executed
Step 'DTSStep_DTSActiveScriptTask_7' was not executed
Step 'DTSStep_DTSActiveScriptTask_8' was not executed
Step 'DTSStep_DTSDataPumpTask_1' was not executed

dbaSQL

ASKER

I am going to award points for your assistance, Rob, as you were very helpful, but the problem is unresolved.
I still insert the full path into the genID field, like this: \\servername\shared\aaaa\Compositions\VFH_ETF_Composition.csv

While I would really like to know how to make this work, I just am unable to wait any longer. So I set up an update on the value after the file has been loaded.
Thank you for your help, Rob.