I have a working solution after trialing several suggestions from here.
First I tried the PowerShell ISE route by saving my query to a file with the concat method in it:
Invoke-Sqlcmd -ServerInstance localhost -Database SupplementalData -InputFile "D:\TestConcatScript.sql" | Out-File D:\test.txt
When I did that it created a file, but it had three extra lines at the top of the text file including a "Column1" header that was added, but was not a part of my script. It also had three '...' characters at the end of each line. It's as if it truncated the data, so this method did not quite work.
Second, I tried using "Query to Text" via SSMS method but I forgot about how that one adds the "execute time" at the bottom of the file, plus some blank lines at the top.
Third, I tried the SSMS "Export Data" option and pasted my query, but it errored out saying it needed a column name: "The data column at the position 1 does not have a name"
The Fourth try worked. I used the SSMS "Export Data" option again. Unchecked the option for "Column Names in First Data Row" added "as Col1" to each of the select statements so it looks like this below. Then set column delimiter to TAB (hoping I wouldn't need to remove them later) and then hit Run, and it created the file exactly as I need. Opened all 1.1 Million records in NotePad++ and a quick inspection looks like it's good to go. I could now save this Export Data SSIS package to use each month.
Thank you for all your tips. This will work. 🙂 I would like to keep trying the PowerShell method at some point because if I had a PowerShell script, I could probably pretty easily upload the file immediately via WinSCP command line after the file gets created (correctly).
Oh, I forgot to add, before I output this table to the text file I recreate the table ordering it by RowID, and so far it's come out with rowID in the correct order each time. I'll still have to ask the vendor what would happen if RowID was not in numerical order and if it would cause any harm.
Select CONCAT('9999', '|', (select TOP 1 replace(convert(varchar,[FILE_DT],106),' ','-') from MonthlyFile), '|', (select count(*) from MonthlyFile) ) as Col1
, '|', [ROW_ID]
, '|', [CONTRACT_NUM]
, '|', [MBR_FIRST_NAME]
, '|', [MBR_LAST_NAME]
, '|', [81 More Columns]
FROM MonthlyData as Col1
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.