CozyRocWe have released 5 brand new data flow script components:
- Google Data Source – Get data from Google Service.
- ADO.NET Command Transformation – Execute SQL command for each row in a data set.
- Range Dictionary Destination – Creates memory-efficient dictionary object for value ranges. The object can then be used in CozyRoc Lookup Plus component.
- Levenshtein Distance Transformation – Calculates the difference between two strings.
- Soundex Transformation – Creates Soundex representation of input string.
Enjoy!
Complex text files
An example of the type of problem we are facing is this: We get a file where the first character determines the format of the rest of the file. To get a complete transaction, you need to process 3 lines. For example we have (this is for a telco system):
1[TAB]123345[TAB]Acme Co
2[TAB]+61290099009
3[TAB]+61380088008[TAB]OUTBOUND[TAB]0.56[TAB]0.09
Where [TAB] means a tab character.
So we get the account number and customer name on line 1, calling number on line 2, the called number, call type, duration and cost on line 3. There is a 1-N relationship from 1 to 2 and 2 to 3.
Currently, we take it into a table as 2 fields , the first character and the rest and then use a cursor to flatten it into rows. This sort of problem is driving us away from SSIS – if we can’t solve this in SSIS, we will either move a lot of out processing to perl or similar, or another ETL tool.
It would be great if you had something that could do this, as M$ dont !
Leigh Kennedy.
www.isql.org
This can be processed in SSIS
Hi Leigh,
This type of layout can be easily processed with simple SSIS source script component. It does require programming, but the same argument goes for processing with perl.