How to extract data from Open Packaging Conventions(.opc) extension files through SSIS

Hi Could you please let me give an idea how to read .OPC data: example for data in the file looks like: f1=1 | f2=2 | f3=3 | f4=4 I wanted to be convert this into table(f1,f2,f3,f4) and values as (1,2,3,4). Currently am us开发者_Go百科ing SSIS to extract the data and read from flatfile connection manager column delimeter as {|} and row delimiter as {cr}{lf} into col1, col2,col3,col4 then identfying the value with"=" string supperator and getting the value.

Is there any betterway to extract from SSIS or any convertion to the source file into .csv/txt or any other format can be done before reading?

Thanks in advance.

I would use a Script transformation, because then you can do all the text parsing using .NET instead, this will probably be much easier than trying to do it using built-in SSIS functions and/or plain TSQL.

Alternatively, you could transform the file to .csv externally using a script in .NET, Perl or whatever, then load the .csv file. You can still use SSIS to run the external script, of course, so you still keep control of the overall process within your package.