Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

substring in a global variable

Posted on 2006-05-30
12
Medium Priority
?
409 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:dbaSQL
  • 7
  • 5
12 Comments
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16792406
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16792490
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?
0
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16792777
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 16792891
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/?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16792960
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

0
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16793440
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16798795
same exact error, except for now it's Error on Line 18
i really need to get around this.
0
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16799053
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16799263
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
0
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 750 total points
ID: 16800564
'  Copy each source column to the destination column
'     DTSDestination("BasketID")=DTSGlobalVariables("gv_FileFullName").Value


Function Main()
    dim fso
    dim Filename
    dim UnderscorePosition
    dim s

    s = DTSGlobalVariables("gv_FileFullName").Value

    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


A "Doh!" moment... the if statement should have been compare equality against 0 (not the otherway round).

Rob
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16800929
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16815545
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.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question