We are trying to insert a pdf file to SQL Server Database as varbinary(max) data type, as part of a migration project from Biztalk to Boomi. As I understand Boomi, doesn’t support this datatype, I followed this approach
- Read the pdf file and store it as Dynamic Process Property
- Read the xml file and store it as Dynamic Process Property, xml contains metadata of the pdf file.
- Call the Wrapper Store Proc passing the DPP’s.
- Wrapper stored proc does the following
- Receive xml and pdf content
- Converts the Char )[pdf Contents), received as Varchar from Boomi to VarBinary(max) data type
- Calls the Main Stored Proc to insert into tables
- Data is successfully inserted in the tables, with the varbinary data looking like 0x2550WNSUDN…453.
- Another application (currently in Prod), reads the binary data from the Table and displays as a pdf for download.
Steps 1-4 execute successfully, However, step 5, is failing as the pdf is failing to be opened.
Shortened process is attached for reference:
- Tried encoding the content in step1 and passing the same in step 3.
- Tried un-encoding the content in step1 and passing the same in step 3.
The above steps works fine with Biztalk (currently in Prod), As Biztalk and SQL Server are both MS products, Biztalk has got an implicit convertor to convert the file to varbinary(Max) and call the stored Proc. In Boomi's case as we are converting a file --> char -->varchar --> varbinary(max).
Any pointers would be appreciated on how this can be handled.