[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1684
  • Last Modified:

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
0
Jason Yousef, MS
Asked:
Jason Yousef, MS
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now