SSIS 2k5 Do I need to make a VB script ?

Question

SSIS 2k5 Do I need to make a VB script ?

pierre.perrin

SSC Enthusiast

Points: 141
More actions
December 28, 2009 at 8:39 am

#136966

Do I need to make a VB script ?
I try to catch the value behind the keyword "datasource" or the keyword "computer" but it's impossible with a classical SUBSTRING because this function needs a start point and a fixed length.:crying:
The keyword value is not at the same place in every line and the length of the value is not the same for each "datasource",
for instance "17-V9-EXPLOIT-MAG-HPM"
and "11-V9-EXPLOIT-MAG-CONDITIONNEMENT":angry:
How can I make a loop to search the string "datasource" or the string "computer" and to catch the value after the research of keyword ?
If I need to make a VB script, I don't know it enough !:alien:
Please, help me and have a happy new year !:-)

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

in TSQL, you could search for where any string begins by using the CHARINDEX function; it is the equivilent of vb INSTR function, or .NET's string.IndexOf("StringToFind")

if it is greater than zero, the string was found; very handy.

here's a TSQL example:

SELECT '17-V9-EXPLOIT-MAG-HPM' AS myVal

INTO #tmp

UNION ALL

SELECT '11-V9-EXPLOIT-MAG-CONDITIONNEMENT' AS myVal UNION ALL

SELECT '11-V9-x41-er5-EXPLOIT-MAG-CONDITIONNEMENT' AS myVal UNION ALL

SELECT 'NOT FOUND' AS myVal

SELECT CHARINDEX('EXPLOIT',myVal),* from #tmp

--results

myVal

----------- -----------------------------------------

7 17-V9-EXPLOIT-MAG-HPM

7 11-V9-EXPLOIT-MAG-CONDITIONNEMENT

15 11-V9-x41-er5-EXPLOIT-MAG-CONDITIONNEMENT

0 NOT FOUND

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pierre.perrin SSC Enthusiast Points: 141 More actions · Answer 2

Hi,

Your solution has two problems :

_ 1 My source is a very big flat file not a SQL Server table

_ 2 There are only two examples of datasource here but actually they are many more

In fact, I want to catch computers and datasources in each ligne of my flat file after researching their with positioning on a keyword (like "datasource" or "computer").

SUBSTRING is unable to do that because it need a numeric position and a fixed length.

I would have liked that SUBSTRING can be set up with the position of the key word with a variable length (the line's length for instance) :

"SUBSTRING([Column 0],LEN([Column 0]),1)" can it be used?

If it's impossible with SSIS, how to do it with VB ?

Thanks and Happy New Year

pierre.perrin SSC Enthusiast Points: 141 More actions · Answer 3

Hello,

I have just found a news tracks by seek on various sites.

I found:

chain = “hello everyone”

mot_cherche = “all”

if instr (1, chain, mot_cherche) > 0 then

wscript.echo “word found in the chain”

end if

That resembles so that I would like to make in my VB.Script.

_chain is the contents total or partial of my rows

_mot_cherche it would be like my key word “computer” or “datasource”

Then for wscript.echo, it is necessary that I carry out certainly another instr () to recover what follows, i.e. the value of the server or the datasource.

I think that I am not far from the result.

How to refer so that instead of “hello everyone”, chain makes to the current line of my flat file extracted in entry from SSIS (this is a kind of Me.something as in ACCESS?)?

Then, do I have to index my rows to read my flat file of A to Z to fin_de_fichier, that programs myself how in VB?

Thank you and happy new year 2010

darth vodka SSCrazy Points: 2367 More actions · Answer 4

why do you need to loop through?

is there a word that always follows "datasource" and "computer"?

if there is, look for those and use CHARINDEX to work it out

it not, you can look for a ";"....like this

SELECT '17-V9-EXPLOIT-MAG-HPM-;Datasource="abcdef123";blah blah blah' AS myVal

INTO #tmp

UNION ALL

SELECT '11-V9-EXPLOIT-MAG-CONDITIONNEMENT;Datasource="somefink";blah' AS myVal UNION ALL

SELECT '11-V9-x41-er5-EXPLOIT-MAG-CONDITIONNEMENT;Datasource="dobededydoo";lah' AS myVal UNION ALL

SELECT 'NOT FOUND' AS myVal

select

case

when a.DataEnd=0 then ''

else substring(a.myVal,a.DataStart+12, a.Dataend-a.datastart-13)

end

as DatSource

,a.*

from

(

SELECT CHARINDEX('Datasource',myVal) as DataStart,CHARINDEX(';',myVal, CHARINDEX('Datasource',myVal)) as DataEnd,

*

from #tmp

) a

my next choice after sql would be to bolt on a new column with the derived column data flow task, but as i remember the syntax of that thing is annoying

darth vodka SSCrazy Points: 2367 More actions · Answer 5

pierre.perrin (1/4/2010)
Hi,
_ 1 My source is a very big flat file not a SQL Server table

ha...

derived column then? same principle as my sql