1

I need some guidance in parsing a file that uses pipe delimiters for each field, has segments and line feeds at end of the row. A sample is pasted below:

Test File I've tried writing a Script Component that separates the column by pipe delimiter. However, I need guidance no how to tackle the various file segments and also the line feed row delimiters.

I've already tried a script component. Unfortunately, the input is Column 0 of my Flat File Source and does not parse any data into the respective columns. How can I use SSIS to ingest the file contents, and also parsing the fields and segments?

SidC
  • 3,175
  • 14
  • 70
  • 132
  • IMHO, just by looking at it, it _might_ be easier to handle with C# script/console app if you can find a pattern (or not), handle things programmatically rather than wrangling with tools that maybe limited (you _already_ wrote a script). The eventual insert(?) into SQL db can be done as well (or `bulk copy`). Hth. – EdSF Oct 09 '20 at 02:24
  • Can you comment on these questions. 1. Your file has a header (first lines with different layout) - should it be parsed as well and does it have different set of columns? 2. Column set - is it fixed? – Ferdipux Oct 09 '20 at 11:51
  • It looks like you have 3 header rows and then your data gets standard w | delimiters and ~ End of Line – KeithL Oct 09 '20 at 12:53
  • Looking at it again. I would do this with C#. You need to handle too many things to rely on a GUI system. – KeithL Oct 09 '20 at 12:55
  • @Ferdipux The header doesn’t need to be parsed. I can map those further downstream. The columns are various widths with pipe | delimiters. Please let me know if additional information is needed. Thanks!, – SidC Oct 09 '20 at 14:14
  • if all you are after are the lines starting with AIA and those lines have all the same number of columns, which appears they do, you can just use a data flow with a flat file source. Source is then set "header rows to skip = 3", uncheck column names in first row option, set column delimiter to vertical bar. It will also then load the last line starting with 'SE', just filter that out after you've loaded it. – Tim Mylott Oct 09 '20 at 15:24
  • if you want all the lines, initially set your flat file connection "header rows to skip=3" so the connection manager will create all the columns for you based on the AIA lines. then change that option back to 0. Load to land/stage table then downstream select and filter based first column of ST, BGM, AIA, etc... and then do your conversions or whatever else you need to do. – Tim Mylott Oct 09 '20 at 15:35

0 Answers0