Integration Manager with No records

[klewis10367]

Has any body figured out how to not run a Scheduled Integration if there are no records to integrate?

 

[gmcelheney]

If your source is SQL, try scheduling your integration with a SQL job, use something like this for the SQL command.

if exists (select top 1 * from DB..your_stage_table)
begin
exec master..xp_cmdshell 'c:\IMDir\IMRun.exe -I your_int'
end

 

[klewis10367]

Good Idea, I will try it and Let you know.

 

[klewis10367]

Nothing Happens, it just says step executed successfully, IM does not run.

 

[gmcelheney]

There is a typo in my syntax. It should be /I, not -I. You may have to kill imrun.exe and cmd.exe in your Task Manager because they are probably still running in the background on the machine where you ran the code I sent. I should have tested this before sending it off.

There's one more question. Is the integration SQL Opt - or Standard Adapter? If it's standard, you have to open GP first so you would need the SQL job to run a batch file that opens GP with a macro and then runs the integration. Hopefully your integration is SQL Optimized and you don't have to worry about this.

Regardless of the type of integration you are using, you won't actually see anything running - unless you look at Task Manager. Double check your IM log for the integration to verify.

 

[klewis10367]

I actually changed the syntax, since I have scheduled jobs with that command line. I am using SQl Optimizer. Still no luck for the Integration. It runs with no errors, but does not integrate.

 

[gmcelheney]

Can you try running the SQL statement from the job in Query Analyzer and see what the result is? It may help in getting this working.

 

[klewis10367]

It doesn't like the Spcae betwenn my Great Plains Directory...it reads :

'I:\Great Plains\IM\IM\IMRun.exe /I NextSBHOA'

how do I fix it with out changing the directory name

 

[klewis10367]

I am getting this error....


'I:\Great' is not recognized as an internal or external command,
operable program or batch file.
NULL

 

[klewis10367]

I got the IMrun.exe to kick off by sharing the IM folder and using a unc path to it:

if exists (select * from tswNextImport)
begin
exec master..xp_cmdshell '\\Mbhqsql02\IM\IMRun.exe /I NextSBHOA'
end

but it just runs and runs with no integration.

 

[klewis10367]

The path with unc will not work because it prompts for a File download for security reasons, therte is no way to bypass it. I can get the original path to work in the run but not in SQl, it just does not like the spaces.

 

[klewis10367]

I got this to run but once again the Imrun just runs with no Integration:
If exists (select * from tswNextImport)
begin
exec master..xp_cmdshell '"I:\Great Plains\IM\IMRun.exe"/I NextSBHOA'
end
Any Thoughts?

 

[gmcelheney]

Ok - good. The next thing to do is monitor the Task Manager to see if cmd.exe and imrun.exe both start, and then stop. If you see them, then try running the command line in a command prompt (Start >> Run, type cmd in the Open txtbox and click OK) and just paste the text

"C:\Great Plains\IM\IMRun.exe" /I NextSBHOA


if it opens and closes you're good. if it opens and is waiting for you to choose an integration, check the spelling, etc.

 

[klewis10367]

This runs fine when I use my windows account...but when you package it in a dts it uses the sql admin account and will not run. It just starts the imrun....any ideas?

 

[gmcelheney]

Check the account on the SQL Server service. It needs to be a Windows user account with permissions on the network drive if you use the SQL sa user for the job. If it is running under Local System, nothing across your network will be accessible to SQL Server.