I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, etc...). The output columns should be something like:

U1   U2  U3                  U4   U5   etc.
null odw odw : CH : de : hom null null   
EUR  sss DE:de:hom           null null   
EUR  crm crm                 null null   


u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v
u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do

Can anyone help with this?

  • The first problem I can see is the etc part. Is there really no upper bound? SSIS is rather persnickety about meta data and so dynamically adding columns isn't going to fly for a Data Flow task.
    – billinkc
    Oct 26, 2012 at 19:13
  • It looks like the Other-Data you provided (prior to Mat's edit) is a semi-colon delimited list if the format of uN=value where N is a series of digits and value is a string. Some of those strings seem to be a colon delimited sub-list of values. The desire is to capture everything after the = up to the next ; Is that accurate? It seems we'd also need a rule for the space succeeding the url as that break in data is followed by a uN key value pair.
    – billinkc
    Oct 26, 2012 at 19:26

1 Answer 1


Usually the way you'd go about this is to have a Script component and enter your VB / C# code to handle this type of input. If you have a variable number of columns than you should forget about SSIS being able to handle this since it doesn't work well with non-finite number of columns (being that tables have a finite number of columns on them).

Having said this, if you have a finite number of columns in the input file, than what you can do is specify the delimiter (the semicolon in the above case) and have the SSIS split the data into the columns and then do the ETL process on them, if needed.

If you have an upper limit of columns, but you get files with less columns than placing a script task before the DFT module to append the extra delimiters that are needed.

Since the first row looks like the header row (column names) you can set the flat file source to skip the first row.

Another alternative would be, if you have a small number of file formats, would be to create different names for each of the log files and have individual DFT modules handle each type of file.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.