I'm trying to use the MS Bulk loader, tMsSqlOutputBulkExe.
Am I correct in assuming that because this must generate a local bulk file to go into SQL Server that it must reside on the same box as SQL Server? I know that this is a pretty fundamental question, but I still have to ask it.
If I were to use SSIS, it allows for remote bulk loading, but it seems this is not possible with Talend. Part of it might just be that I need write access on the remote box (where SQL Server resides). I have that access, but still unable to move forward.
Is anyone successfully using this component on different environments (where TOS is on a local box and SQL Server is remote)? Please give me some suggestions.
Thanks
I've work a few times with MSSQL Bulk in Talend.
The way I made it work:
1. Produce the file with tMSSQLOutputBulk (not BulkExec!).
2. using FTP or any other file transfer method (network shared folder also works) I sent the file to a place where the SQL Server instance can see the file.
3. Using tMSSQLBulkExec, you fill the Remote Filename with the path to the file as you would type if you were doing a bulk operation locally in the SQL Server. For example:
ETL job is running in host "ServerETL". Produce the file there and send it to "ServerFTP" using tFTPPut. The SQL Server is running in "ServerSQL" and it can access the "ServerFTP" using a shared folder. So I would setup the Remote Filename in the tMSSQLBulkExec with: "\\\\ServerFTP\\FTPRoot\\BulkFile.txt".
Remember this is a limitation from MSSQL Server. It requires the BulkFile to be accessible from SQL Server host machine to do bulk loads.
Regarding the same topic, I have also ammended the tMSSQLBulkExec to add a new property: Fire Triggers. Let me know if you need anything in that direction also. I would like to send the code to Talend, but I don't know exactly how to do.
Regards,
Thiago
Remember that the BulkExec takes place on the remote SQL Server so the path to the file must be from the point of view of that remote server i.e. if you use "C:/Temp/file.csv", the job will try to find the file on the C: drive of the SQL Server.
So if you're placing the file on your local machine, you need to use your machine's name in the file path (e.g. "//Machine003/Temp/file.csv") and make sure that the folder is shared and accessible to the login being used by SQL Server to run the bulk exec.
See
https://community.talend.com/t5/Design-and-Development/tMSSqlOutputBulkExec/td-p/107915.
The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp utility performs the following tasks: Bulk exports data from a SQL Server table into a data file. Bulk imports data from a data file into a SQL Server table. liteblue login
The BulkExec use it on the remote SQL Server so the path to the file must be from the point of view of that remote server i.e. if you use "C:/Temp/file.csv", the job will try to find the file on the C: drive of the SQL Server. Liteblue.USPS.Gov
same question. Could you share your experience about this question if you have idea. Bulk exports data from a SQL Server table into a data file. Bulk imports data from a data file into a SQL Server table. liteblue usps gov
If you are using Enterprise Version of Talend Example, Data Fabric, in that case, you will have an Execution Server option in Run Tab. Where in you can set a Remote server IP (instead of LocalHost) which can be accessed by both your SQL Server (SSMS) as well as Talend.
But if you are using Open Studio version then, in that case, you will have to install Talend and SQL on the same machine. Make sure permissions necessary for Bulk Operations is enabled in SSMS for your Service Account / Windows Authentication, then even with LocalHost pointing to any local drive(C drive) file. Bulk insert operation in Talend should work.
