I am writing an SSIS package to handle the output from a SQL query and my intent is to have SQL/SSMS generate a pipe delimited file. I understand that normally this can be achieved with the below SSMS setting specifications:
Tools--> Options --> Query Results --> Sql Server --> Results to Text. Change output format from Tab Delimited to Custom Delimited. Put a Pipe in the Custom Delimiter Box. Go back to your query select Query --> Results to File. Execute file, Give it a Disk path and a name.
As I'm trying to automate this via SSIS what I am trying to do instead if find if it's possible to specify pipe delimited columns in my file via the SQL query directly. Is this possible? I've found a few 'workarounds' to reproduce this but none of them have seemed all that graceful. Thank you.
The expected production of a pipe delimited file from SQL query is not natively supported with a built in delimiter option, per Devlin Carnate.
Solutions for such queries can be achieved via a number of different ways including SSIS connection manager details as well as fancier SQL queries that are, inevitably, workarounds options.