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…
Stambia Data Integration allows to work with Microsoft SQL server (MSSQL) databases to produce fully customized Integration Processes.
The database structure can be entirely reversed in Metadata and then used in Mappings and Processes to design and adapt the business rules to meet the user's requirements.
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.
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.
The only thing to do on the Load template is to verify that the Enable File Transport option is checked.
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.