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 http://www.talendforge.org/forum/viewtopic.php?id=29553.