"William Billington" <william_bill26@hotmail.com> wrote in message
news:%23iWLrwUCEHA.1812@TK2MSFTNGP12.phx.gbl...
> I am pretty new to VB and scripting so any help would be greatly
> appreciated.
>
> I have to import some files that are fixed width but which contain Header
> information. I can extract the column info fine by telling DTs to start
at
> Row #, but I need to capture information from row 6 in the Header , which
is
> the last header row before the column names e.g.
>
> Line 1 Report Name
> Line 2 Report Date
> Line 3 Report Paramaters
> Line 4 Machine Details
> Line 5 Other details
> Line 6 Batch : Batch No 9, February 2004
>
> I only need to capture the actual Batch No e.g. 9 and the date element as
I
> need to insert these into columns in the same table. The files will
always
> be in the same format. I have been playing around but I cant seem to read
> in just what I want ie the numeric and date.
>
> Thanks in advance
>
> Bill
>
>

"William Billington" <william_bill26@hotmail.com> wrote in message
news:ehbJd$hCEHA.2656@TK2MSFTNGP12.phx.gbl...
> Allan
>
> Thanks, but I really am a rookie at this. I am aware that I should be
using
> the ReadFile and ReadLine i nVBScript, my problem is that I do not know
VB
> or VBScript and I am strugglng with the syntax as every 'post' I have seen
> refers to the FSO, but I cant find an example of the syntax for using it
to
> read in a line and parse it. I have also just realised that I will have
to
> import the Machine Details on line 4 and then get the other details from
> line 6 and insert these into the table - which has a standard Transfrom
Task
> mapped to the columns in this report with some calculations in ActiveX
> Script..
>
> As fas as I know, I couldnt even use a Global Variable for this as I would
> be importing several files simultaenously and I think the variables would
> be overwritten. I am supposed to have this working by tomorrow - the
> developers wh worked on the other project which produces these files told
us
> it would be easy - well only if you know programming. I have managed to
do
> everything else by using Books on Line and postings - But I am now totally
> at a loss. (I am self-taught on the database side)
>
> Thanks for any help - I dont even have a book on this I can refer to.
>
> Bill
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:%23rm8SOmCEHA.1128@TK2MSFTNGP11.phx.gbl...
> OK
>
> You are most likely going to want a loop to go around the text files.
> You can use Global Variables yes. You then assign the values of the
Global
> Variables to your destination fields in the transform.
>
> How do I read lines 4 and 6
>
> Text File looks like this say
>
> I am line 1
> This is line 2
> We are getting close now line 3
> I want this line here 4
> I know we are getting somewhere now 5
> Finally this is the last header row
>
> 1,2,3,4,5,6
> 2,3,4,5,6,7
>
> In an Active Script task you could do this
>
> Const ForReading = 1
> Dim fso, f, pkg, txtFile, Line4, Line6
> Set fso = CreateObject("Scripting.FileSystemObject")
> set pkg = DTSGlobalVariables.Parent
> 'Set the variable to the name of the file
> txtFile = pkg.Connections("Text File (Source)").DataSource
>
> 'Open the text file for reading
> Set f = fso.OpenTextFile(txtFile, ForReading)
>
> 'Skip lines we do not want
>
> f.SkipLine
> f.SkipLine
> f.SkipLine
> Line4 = f.ReadLine
> f.SkipLine
> Line6 = f.ReadLine
>
> msgbox Line4
> msgbox Line6
>
>
>
> --
>
> ----------------------------
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.allisonmitchell.com - Expert SQL Server Consultancy.
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "William Billington" <william_bill26@hotmail.com> wrote in message
> news:ehbJd$hCEHA.2656@TK2MSFTNGP12.phx.gbl...
> > Allan
> >
> > Thanks, but I really am a rookie at this. I am aware that I should be
> using
> > the ReadFile and ReadLine i nVBScript, my problem is that I do not know
> VB
> > or VBScript and I am strugglng with the syntax as every 'post' I have
seen
> > refers to the FSO, but I cant find an example of the syntax for using it
> to
> > read in a line and parse it. I have also just realised that I will have
> to
> > import the Machine Details on line 4 and then get the other details from
> > line 6 and insert these into the table - which has a standard Transfrom
> Task
> > mapped to the columns in this report with some calculations in ActiveX
> > Script..
> >
> > As fas as I know, I couldnt even use a Global Variable for this as I
would
> > be importing several files simultaenously and I think the variables
would
> > be overwritten. I am supposed to have this working by tomorrow - the
> > developers wh worked on the other project which produces these files
told
> us
> > it would be easy - well only if you know programming. I have managed to
> do
> > everything else by using Books on Line and postings - But I am now
totally
> > at a loss. (I am self-taught on the database side)
> >
> > Thanks for any help - I dont even have a book on this I can refer to.
> >
> > Bill
> >
> >
>
>

"William Billington" <william_bill26@hotmail.com> wrote in message
news:O%23onhomCEHA.2908@TK2MSFTNGP09.phx.gbl...
> Allan
>
> Thanks for your time and help. I will go and try this now, but can I
> clarify :
>
> I have 10 files comming in each with a TextFile Source and then a
> Transformation, each having its own connection.
> Do I put this Script Task before the Text File Source for each of my
> imports.
> Will I need seperate Global Variables for each of my text file imports for
> this task to write to?
>
> Thanks
>
> Bill
>
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:%23rm8SOmCEHA.1128@TK2MSFTNGP11.phx.gbl...
> > OK
> >
> > You are most likely going to want a loop to go around the text files.
> > You can use Global Variables yes. You then assign the values of the
> Global
> > Variables to your destination fields in the transform.
> >
> > How do I read lines 4 and 6
> >
> > Text File looks like this say
> >
> > I am line 1
> > This is line 2
> > We are getting close now line 3
> > I want this line here 4
> > I know we are getting somewhere now 5
> > Finally this is the last header row
> >
> > 1,2,3,4,5,6
> > 2,3,4,5,6,7
> >
> > In an Active Script task you could do this
> >
> > Const ForReading = 1
> > Dim fso, f, pkg, txtFile, Line4, Line6
> > Set fso = CreateObject("Scripting.FileSystemObject")
> > set pkg = DTSGlobalVariables.Parent
> > 'Set the variable to the name of the file
> > txtFile = pkg.Connections("Text File (Source)").DataSource
> >
> > 'Open the text file for reading
> > Set f = fso.OpenTextFile(txtFile, ForReading)
> >
> > 'Skip lines we do not want
> >
> > f.SkipLine
> > f.SkipLine
> > f.SkipLine
> > Line4 = f.ReadLine
> > f.SkipLine
> > Line6 = f.ReadLine
> >
> > msgbox Line4
> > msgbox Line6
> >
> >
> >
> > --
> >
> > ----------------------------
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.allisonmitchell.com - Expert SQL Server Consultancy.
> > www.SQLDTS.com - The site for all your DTS needs.
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> > "William Billington" <william_bill26@hotmail.com> wrote in message
> > news:ehbJd$hCEHA.2656@TK2MSFTNGP12.phx.gbl...
> > > Allan
> > >
> > > Thanks, but I really am a rookie at this. I am aware that I should be
> > using
> > > the ReadFile and ReadLine i nVBScript, my problem is that I do not
know
> > VB
> > > or VBScript and I am strugglng with the syntax as every 'post' I have
> seen
> > > refers to the FSO, but I cant find an example of the syntax for using
it
> > to
> > > read in a line and parse it. I have also just realised that I will
have
> > to
> > > import the Machine Details on line 4 and then get the other details
from
> > > line 6 and insert these into the table - which has a standard
Transfrom
> > Task
> > > mapped to the columns in this report with some calculations in ActiveX
> > > Script..
> > >
> > > As fas as I know, I couldnt even use a Global Variable for this as I
> would
> > > be importing several files simultaenously and I think the variables
> would
> > > be overwritten. I am supposed to have this working by tomorrow - the
> > > developers wh worked on the other project which produces these files
> told
> > us
> > > it would be easy - well only if you know programming. I have managed
to
> > do
> > > everything else by using Books on Line and postings - But I am now
> totally
> > > at a loss. (I am self-taught on the database side)
> > >
> > > Thanks for any help - I dont even have a book on this I can refer to.
> > >
> > > Bill
> > >
> > >
> >
> >
>
>

"William Billington" <william_bill26@hotmail.com> wrote in message
news:uN0PlHnCEHA.1452@TK2MSFTNGP09.phx.gbl...
> Allan,
>
> They are going into separate tables, so Script before the TextFile Source?
> Do I change the Source in each Task?
>
> 'Set the variable to the name of the file
> txtFile = pkg.Connections("Text File (Source)").DataSource e.g.
("Text
> File (abcde23)").DataSource
>
> Thanks again Allan
>
> Bill
>
>