Introduction
The "LOAD Rdbms to Mssql (with BULK)" Template gives the opportunity to use the SQL Server BULK statement during LOAD steps.
It extracts source data to a file, and then imports the file into SQL Server with the BULK statement.
It offers better performances than the LOAD Rdbms to Rdbms template for large amount of data. It can so be interesting to use it in this case.
If the runtime is installed on the same machine as SQL Server, the process will work without problems, as the generated files for BULK load will be on the same machine.
But if not, the template can be configured to transfer the file to the server.
Configuring the SQL Server metadata
First, a FTP Server has to be installed on the same machine as SQL Server, because the template will use FTP to send the file.
When it's done, create a FTP Metadata and a directory :
Note : The Absolute Path must be set. The template will use this value to find the file.
Next, drag and drop the directory on the SQL Server metadata.If you put it on the schema, you'll be able to use the Bulk load for all the tables. Otherwise put it only on the table on which you need it.
Finally, rename the ftp metadata link to TARGET_FTP. The name is important for the tamplate to recognize it.
Configuring the LOAD template
The only thing to do on the Load template is to verify that the Enable File Transport option is checked.
Execution
During execution you might encounter this exception :
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load because the file "C:\Temp\test\data_L1_T_CUSTOMER_BULKLOADED.dat" could not be opened. Operating system error code 5(Access is denied).
This is due to SQL Server not being able to open or modify the file because of right problems. Check if SQL Server has read/write rights on the folder containing the file.