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
LVL 21
Jason Yousef, MSSr. BI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
you could import to a temp/staging table and then only select and parse the rows starting with numerics...
0
Anthony PerkinsCommented:
Since I suspect the headers and footers have a different structure you will have to do it like this:
Handling different row types in the same file
http://www.sqlis.com/post/Handling-different-row-types-in-the-same-file.aspx
0
Megan BrooksSQL Server ConsultantCommented:
Normally I would say to use a conditional split, but I can't off-hand like think of a way to test for numbers using an SSIS expression. The alternative is a script transformation task.
What you need is a script transformation with a synchronous output that specifies an ExclusionGroup.
  1. Create a data flow task
  2. Add an input source to it (your fixed-width flat text file)
  3. Add the script component and connect it to the input source
  4. Edit the script component -- select the Inputs and Outputs pane, select Output 0 by clicking on it, and set the ExclusionGroup property to "1" (or any nonzero value)
  5. While still editing the script component, select the Script pane and click Edit Script...
  6. In the script editor, edit the Input0_ProcessInputRow method to look something like the example below, where FieldName is the name of the input column you want to test (for numeric). It must be a string type, or else you must convert it to string.
  7. Create the data output and connect it to the script component
When a single exclusion group is defined for a synchronous transformation, data is written only for rows on which Row.DirectRowToOutputxxx is called. (If you were to define more than one exclusion group, you could send different rows to different outputs.)
The Double.TryParse() function is used in this example to check for numbers. You could do it a different way, depending on what your data actually looks like. In fact it is likely that you will need to change this part. What does your data look like? Do you need to test an entire field to see if it is numeric, or just the leading characters of a field?

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Double num;
        if (Double.TryParse(Row.<FieldName>, out num))
            Row.DirectRowToOutput0();
    }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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

0
Megan BrooksSQL Server ConsultantCommented:
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.
0
Anthony PerkinsCommented:
Be carefull with using IsNumeric, it does produce false positives.
0
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
0
Anthony PerkinsCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.