You need the over X12N layout.

AND

You need the companion guide.

AND

More than likely, you need to speak with someone at the generating end, because (more than likely) the layout + the companion guide are incomplete, ambiguous, mutually contradictory, and/or inaccurate in at least one detail.

No, you should never need SSIS.

No, you _cannot_ predict that || won't show up in the file unless it is explicitly forbidden.

SQL Server is, quite frankly, not the only tool to be using unless you nail down, in writing from the generator of the file (and the sender if that's different than the generator) all details of the format. As this is your first file, you're unlikely to guess all the weird edge cases that may apply. Your fields are likely to be * separated, but your segments may be ~ separated, ~LF separated, ~CRLF separated, or ??? separated. In particular, the first three options may change file to file or environment to environment or even within the same file (unlikely but possible).

If you can nail down the separators, and be _explicit_, not implicit, about it (most humans ignore LF and/or CRLF completely, not even noticing they're there or that there's a difference... and BCP/BULK INSERT with a format file cares), then you have a chance. Worse, some people will simply add in LF or CRLF when they want to look at the file by hand, and then take them out when they don't.

Pull out a hex editor like HxD[/url] and start looking at sample files, comparing them, byte by byte, field by field, segment by segment, with the layout and companion guide; without one, you're lost, because you assume that the people that coded the file generation used the same documentation you have accurately and completely. In most cases, they didn't even have the documentation you do, because both code and documentation changed over time, and not necessarily in lockstep.

Also, I'd recommend no longer using the term 'EDI file' - it's just a data file, and I think you're imposing rules in your mind that don't exist in the minds of others - EDI is just electronic data interchange; that can be a packed decimal EBCDIC file, or a 0 byte file where all value is in the filename, which is encrypted - 'EDI' means data is being interchanged, that's all, no specifics. Personally, I'd also say rename the extension to .txt if it's text data (as you've indicated it likely is), but I'm a little old-fashioned, and there's no actual issue with using the .edi extension.