Link to home
Start Free TrialLog in
Avatar of Jason Yousef
Jason YousefFlag for United States of America

asked on

Using SSIS, Flat File source, Fixed Width

Hello SSIS Gurus,
I'm trying to import data from a fixed width TXT file, that's easy I know !

but what If i want it to get the rows which starts with NUMBERS only?

because the TXT file has some headers and footer every 10 records !!!

Is that can be done? is it an optiong that I don't know about?

Thanks so much in advance and happy easter
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

you could import to a temp/staging table and then only select and parse the rows starting with numerics...
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jason Yousef

ASKER

Thanks All for your help, God bless you for sharing the knowledge... I've used another simpler script and a diverted columns task..

rscowden: your script didn't work, don't know why...didn't give it much troubleshooting time.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If IsNumeric(Left(Row.ID, 6)) Then
            Row.IsNumeric = True
        Else
            Row.IsNumeric = False
        End If



    End Sub

Open in new window

Different scripting language for one thing. The "TryParse()" is a .Net Framework equivalent of VB's IsNumeric(). The main thing is that you can filter right in the script task by giving the script component output a nonzero ExclusionGroup property value and calling DirectRowToOutputxxx only for the rows you want to pass through.
...Unless you really wanted all the rows to remain in the pipeline.
Be carefull with using IsNumeric, it does produce false positives.
rscowden:I didn't know that :)

acperkins:  that would be a problem :) lol
I hope that never happen....

Thanks for telling me, I was reading this

http://ask.sqlservercentral.com/questions/1025/bug-in-mssql-function-isnumeric
The link you posted is to the T-SQL version of ISNUMERIC(), but you are using the IsNumeric() method from .NET.  Different animals, but the answer is the same: It is not a bug, that is the way it works.  For example, IsNumeric() supports exponential numbers so:
123456d12 is valid.  It may not be to you, but it is perfectly valid and not a bug.  There are many other examples like this.