The parameter -t putdata also enables the automatic uploading of files to a Media or Mediaset field of a specific table in Business Central. The uploaded files are saved in Tenant Media and referenced with a GUID. This GUID is then entered in the corresponding media or media set field of the target table.
Command syntax
DataMigratePro -t putdata -s <TableNo> -i "<SQL-Befehl>" --automapping <Tabellennummer>or with a manual mapping file:
DataMigratePro -t putdata -s <TableNo> -i "<SQL-Befehl>" -m <Pfad zur Mapping-Datei>Parameter description
-s <TableNo>
Specifies the target table in Business Central into which the files are imported.-i "<SQL-Befehl>"
Defines the SQL command that provides the required information for the file transfer.--automapping <Tabellennummer>
Automatically creates a mapping for the table and field assignment.-m <Pfad zur Mapping-Datei>
Specifies a manual mapping file if--automappingis not used.
Functionality
- Execute SQL query
The SQL command transferred via-iprovides the required information for the file transfer.
The following three or four columns must be returned for the relevant Media or Mediaset field in the Business Central target table:<Feldname>.Description→ Description of the document (e.g. file name)<Feldname>.MimeType→ MIME type of the file (e.g.image/jpeg)- Depending on the source:
<Feldname>.FilePath→ File path (if the file comes from the file system)<Feldname>.Content→ File content asBLOB(if the file originates from a database)
- In addition, the SQL command must return all primary key fields of the target table in order to assign the file to the correct data record.
- Upload files
- If
FilePathis used, the file is loaded from the specified path. - If
Contentis used, the file is transferred directly from the SQL query asBLOB. - The file is saved as a blob in Tenant Media and receives a GUID.
- If
- Link to the Business Central table
- The generated GUID is saved in the corresponding Media or Mediaset field.
- If it is a media set, the new medium is added to the tenant media set.
Case 1: Upload files from the file system to the [Document Attachment] table
Preparatory work
CREATE TABLE [dbo].[ContractDocuments](
[Contract Code] [nvarchar](20) NOT NULL,
[FileName] [nvarchar](255) NOT NULL,
[Extension] [nvarchar](10) NOT NULL,
[Path to File] [nvarchar](512) NOT NULL,
CONSTRAINT [PK_ContractDocuments] PRIMARY KEY CLUSTERED
(
[Contract Code] ASC,
[FileName] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[ContractDocuments] ([Contract Code], [FileName], [Extension], [Path to File])
VALUES
(N'CONTRACT001', N'Arbeitsvertrag_Befristet.pdf', N'pdf', N'C:\Mustervertraege\Arbeitsvertrag_Befristet.pdf')
,(N'CONTRACT002', N'Arbeitsvertrag_Unbefristet.pdf', N'pdf', N'C:\Mustervertraege\Arbeitsvertrag_Unbefristet.pdf')
,(N'CONTRACT003', N'Ausbildungsvertrag.pdf', N'pdf', N'C:\Mustervertraege\Ausbildungsvertrag.pdf')
,(N'CONTRACT004', N'Kulturvertrag.pdf', N'pdf', N'C:\Mustervertraege\Kulturvertrag.pdf')
,(N'CONTRACT005', N'Bauvertrag.pdf', N'pdf', N'C:\Mustervertraege\Bauvertrag.pdf')
GOPreparation must also be carried out in the mapping configuration or a manual mapping must be created. Here is the first variant, in which the source configuration is based on the SQL query and cannot be derived from the structure of NAV and must therefore be entered manually.
The SQL query returns the primary key ([ID],[Table ID],[No_]) and the file information for the media field [Document Reference ID]:
SELECT ROW_NUMBER() OVER(ORDER BY [Contract Code]) [ID]
, 8052 [Table ID] -- Debitorenverträge
, [Contract Code] [No_]
, 21 [Document Type] -- Service Contract
, 0 [Line No_]
, GETDATE() [Attached Date]
, [FileName] [File Name]
, [Extension] [File Extension]
, [FileName] [Document Reference ID.Description]
, CASE WHEN [Extension] = 'jpg' THEN 'image/jpeg' WHEN [Extension] = 'png' THEN 'image/png' ELSE 'application/octet-stream' END AS [Document Reference ID.MimeType]
, [Path to File] [Document Reference ID.FilePath]
FROM [dbo].[ContractDocuments]Command for execution:
DataMigratePro -t putdata -s 1173 -i "SELECT ROW_NUMBER() OVER(ORDER BY [Contract Code]) [ID], 8052 [Table ID], [Contract Code] [No_], 21 [Document Type] -- Service Contract, 0 [Line No_], GETDATE() [Attached Date], [FileName] [File Name], [Extension] [File Extension], [FileName] [Document Reference ID.Description], CASE WHEN [Extension] = 'jpg' THEN 'image/jpeg' WHEN [Extension] = 'png' THEN 'image/png' ELSE 'application/octet-stream' END AS [Document Reference ID.MimeType], [Path to File] [Document Reference ID.FilePath] FROM [dbo].[ContractDocuments]" --automapping 1173Case 2: Uploading files from a BLOB field in the database
If the files are stored in an SQL database as BLOB, .Content must be used instead:
SELECT
[Contract Code] AS [Code],
[FileName] AS [Documents.Description],
[MimeType] AS [Documents.MimeType],
[FileContent] AS [Documents.Content]
FROM [Some Table with Document Information]
Command for execution:
DataMigratePro -t putdata -s 50000 -i "SELECT ..." --automapping 50000Prerequisites
- The target table in Business Central must contain a Media or Mediaset field.
- If
FilePathis used, the files must be physically available on the server. - If
Contentis used, the file must already exist as a BLOB in the SQL database. - The Business Central instance must be configured for the use of media/mediaset fields.
Supported file formats
- Pictures:
jpg,png,gif,bmp,tiff - Documents:
pdf,txt,docx