Jason Yousef
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
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
you could import to a temp/staging table and then only select and parse the rows starting with numerics...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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.
...Unless you really wanted all the rows to remain in the pipeline.
Be carefull with using IsNumeric, it does produce false positives.
ASKER
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
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.
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.