Concatenate documents after creation

We want multiple templates that can be controlled individually and the documents generated based on these templates have to be concatenated afterwards.

The idea behind this is that a remote system can make multiple requests to generate a document. Then the systems uses a separate webservice to retrieve all the documents of which it states the RequestID’s. The result should be returned as a single PDF as base64.

Below you find an example to load 4 documents and concatenate these in one PDF. The comments state other options that can be used.

declare @Files table ([File name] nvarchar(max))
insert into @Files
select '\Correspondence19_01_18\Document_1_ALFKI_1604523830248_1.pdf'
insert into @Files
select '\Correspondence19_01_18\Document_1_ANATR_1604523830248_2.pdf'
insert into @Files
select '\Correspondence19_01_18\Document_1_ANTON_1604523830248_3.pdf'
insert into @Files
select '\Correspondence19_01_18\Document_1_BERGS_1604523830248_5.pdf'

-- Action is case sensitive
select 'AddPdf' as [Action], '{StartupPath}'+[File name] as [Source], null as [Destination]                          -- TIF,DOC,DOCX,XLS,XLSX files are also supported file types to be added and converted to PDF
from @Files
union all
select 'SavePdf' as [Action], null as [Source], '{StartupPath}'+'JoinedDocuments.pdf' as [Destination] -- use SavePDFBase64 to store as BASE64 in  @Paremeter_Base64

To execute this code it needs to be placed in a command and that command then has to be executed through a task. Within the task the command has to be executed with a file_system command.

Example:
Place code above in a command e.g. 1234.
Then execute in a task: File_System;1234

Note:
The select ‘AddPdf’ and ‘SavePdf’ statements above have to be the last code in the command!