Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Using Bulk Load on a distant server with enableFileTransport option

    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 :

    ftpMetadata

    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.

    sqlServerMetadata

    Finally, rename the ftp metadata link to TARGET_FTP. The name is important for the tamplate to recognize it.

    rename

     

    Configuring the LOAD template

    The only thing to do on the Load template is to verify that the Enable File Transport option is checked.

    template

     

    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.

     

     

    Subcategories

    Articles

    Suggest a new Article!