This one stumped even Salesforce.com, but I didn’t give up!

Data loader is a free tool provided by Salesforce.com and has a command line interface and can be called from within a batch file. The problem is, within the batch file it forces a change directory so it is next to impossible to call the batch file, within a SQL Job as a CmdExec, from a remote server.

There are plenty of blog questions asking for a solution. I’d even called our highest Technical Support person at Salesforce.com, with a very intruiging “Wow, I don’t think you can do this or if you can I don’t know how you would” response. So reassuring.

I stumbled on a blog post that hinted a solution might be calling a windows scheduled job on the remote server. After pouring over Google solutions for hours (ok, probably closer to a whole day) I managed to incorporate this solution and will outline the steps here, for those who are just that bored or are looking for this insight.

Server A
I needed to put the Sales Force data loader, batch and configuration files, as well as the data files on a stand-alone server (Windows 2008 Server).
Server B
Separately, I had a SQL node (2005) on a Windows 2003 server.

I had to pull in data from sales force through the data loader (which downloads .csv files), pull the data into SQL tables, compare and transform, then push data back through the data loader to Salesforce.com.

The SQL job steps are similar to this:
1 – Set all bit files to zero (ActiveX script)
2 – Pull Data from SFDC (scheduled task)
3 – Check for download completion (stored proc)
4 – Pull into staging database (SSIS Package)
Etc…

I created windows scheduled jobs (using the command line because I needed to initiate backwards compatibility for the scheduled jobs, enabling them to be remotely called). So there are scheduled jobs on Server A calling the batch file which pulls and/or pushes through the data loader command line tool (syntax: schtasks /tn “ScheduledTaskName” /tr “c:\path to batch file” /st 01:00 /v1 (you can change the task once created, remove the scheduled time, the v1 switch is to make it backwards compatible).

Note – Step 1 above: The problem with calling scheduled jobs through SQL Server is you issue the command and it returns, immediately, a success – unless it is unable to start the task. Which means the command has been issued but not necessarily that the batch file which was called completed. This can take minutes, depending upon the size of the .csv you are attempting to pull down or push up. To solve this problem, I begin my series of SQL Steps with an ActiveX script which writes a series of text files with a zero (0) in them, then I have a stored procedure which I pass in the name of the .txt file as the parameter and it loops and waits for 60 seconds, until it finds the 1 in the file.


Step 2 - I run this through a SQL Job – operating system (CmdExec) step using this syntax: schtasks /Run /S ServerName /TN "ScheduledTaskName"

Step 3 – I created a stored procedure which checks the contents of the file, the call would be similar to: EXEC [dbo].[pr_ReadFinishFileBit] '\\server\c$\folder\filetocheck.txt' and the contents of the proc are similar to:

CREATe PROCEDURE [dbo].[pr_ReadFinishFileBit]
(@filetocheck varchar(255))
AS
BEGIN
SET NOCOUNT ON;

set @filetocheck = 'type ' + @filetocheck

DECLARE @output TABLE (bout bit)
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
DECLARE @check bit
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
DECLARE @ctr int
SET @ctr = 0

WHILE @check = 0
BEGIN
SET @ctr = @ctr + 1
IF @ctr = 60 --too long, error out
BEGIN
RAISERROR('Error - download/upload not complete within 60 minutes',16,1)
BREAK;
END
--wait for one minute, check again
WAITFOR DELAY '0:1:00'
DELETE FROM @output
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
END

END

Step 4 – This is a pretty straight forward SSIS package which pulls from the .csv files into a staging database – nothing fancy so I won’t bore you with the details.

If you find yourself in this same situation and need more tedious details, please feel free to post a comment and I’ll attempt to help where I can.

Peace out.