Insert custom header row in BCP output

One option you might consider is separating the two. Create one file with your export and all fields. The other file just has your header. The last step would be to combine the two with something like this:

REM create the header file
ECHO 100|0|SSO|UPDATE|EN|N|N| >"MyExport.txt.header"
REM append the bcp export to the header file
TYPE "MyExport.txt">>"MyExport.txt.header"
REM rename the header file back to the export file name
MOVE /y "MyExport.txt.header" "MyExport.txt"

You mentioned the export was done daily. Is there an SQL job schedule to do this? This could be a step added to the job using the Operating system (CmdExec) type. It doesn't have to be an "external" process if I understand you correctly. All of the code can be entered into the job step.

If you're not familiar with it, the other thing you can look into is SQLCMD. Bottom line is I believe you are correct: What you want probably can't be done using bcp directly in one step.

You can create a CMD script to create a temp header row file, run BCP, and then append the BCP output to the temp header file. This would be called via xp_cmdshell, just like the existing call to BCP in your current setup.

Here is the CMD script, which I named AddHeaderToExportFile.cmd. It takes two parameters:

1. The filename.
2. The header row. If it changes, you only need to update the Stored Procedure.

Just create a new text file in Windows Explorer, then replace the name (including the .txt extension) with AddHeaderToExportFile.cmd. Then edit AddHeaderToExportFile.cmd and paste in the following code and save it.

AddHeaderToExportFile.cmd:

@ECHO OFF

SET TempHeaderRowFile="%TEMP%\TempHeader.txt"
SET TempOutputFile="%TEMP%\TempOutput.txt"


BCP "EXEC [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout %TempOutputFile% -c -C 1252 -T -t "|"

ECHO %~2 >  %TempHeaderRowFile%

REM Concatenate Header + BCP_Output -> @FileName
REM /V = Verifies that new files are written correctly.
REM /Y = Suppresses prompting to confirm you want to overwrite an existing destination file
REM /B = treat files as Binary (else you get an extraneous CHAR(26) at the end)
COPY /V /Y /B %TempHeaderRowFile% + %TempOutputFile% %1

REM Delete the temporary Header and BCP output files
IF EXIST %TempHeaderRowFile% DEL /Q %TempHeaderRowFile%
IF EXIST %TempOutputFile% DEL /Q %TempOutputFile%

Adapting your original script to call the new CMD script, your new SQL should be something like:

Stored Procedure:

--employee export
DECLARE @FileName NVARCHAR(500)
SET @FileName = N'\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' +
      REPLACE(REPLACE(REPLACE(
           CONVERT(NVARCHAR(19), CONVERT(DATETIME, GETDATE(), 112), 126),
                              N'-', N''), N'T', N''), N':', N'') +
      N'.txt';

DECLARE @Command NVARCHAR(4000),
        @Header   NVARCHAR(500);

SET @Header = N'100|0|SSO|UPDATE|EN|N|N|';

SET @Command = N'C:\TEMP\BCP\AddHeaderToExportFile.cmd "' +
               @FileName +
               N'", "' +
               REPLACE(@Header, N'|', N'^|') +
               N'"';

EXEC xp_cmdshell @Command; --, NO_OUTPUT;

---

OR, you can create a text file to hold the header row value, and then skip the CMD script altogether and use multiple calls to xp_cmdshell to accomplish the same thing:

--employee export
DECLARE @FileName NVARCHAR(500),
        @HeaderFile NVARCHAR(500);
SET @FileName = N'\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' +
      REPLACE(REPLACE(REPLACE(
           CONVERT(NVARCHAR(19), CONVERT(DATETIME, GETDATE(), 112), 126),
                              N'-', N''), N'T', N''), N':', N'') +
      N'.txt';
SET @HeaderFile = N'\\someFileServer\public\someFolder\header.txt'; -- static header row

DECLARE @Command NVARCHAR(4000);

SET @Command = N'BCP "EXEC [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout ' + @FileName + 'tmp -c -C 1252 -T -t "|"';
EXEC xp_cmdshell @Command; --, NO_OUTPUT;

SET @Command = N'COPY /V /Y /B ' + @HeaderFile + N' + ' + @FileName + N'tmp ' + @FileName;
EXEC xp_cmdshell @Command; --, NO_OUTPUT;

SET @Command = N'IF EXIST ' + @FileName + N'tmp DEL /Q ' + @FileName + N'tmp';
EXEC xp_cmdshell @Command; --, NO_OUTPUT;