Execute Oracle packages (control-M) using SQL Agent and Powershell

Question

Execute Oracle packages (control-M) using SQL Agent and Powershell

Lord Slaagh

SSCertifiable

Points: 5710
More actions
June 6, 2016 at 9:26 am

#314360

Hello,
I need to execute a bunch of Control-M Oracle jobs and packages using POWERSHELL. These jobs will be scheduled using SQL Agent. I have the following code (see below), and I am assuming that I can just plug this into a SQL Agent job? Can anyone assist?
Any help would be greatly appreciated.
powershell d:\util\ControlM\DEV\Tools\Wrapper.ps1 -ConfigPath %%configpath -Environment DEV -Country US -SqlFile D:\util\ControlM\DEV\ctrlm\assets\sql\SCRIPT.sql -ConnectionStringProperty ConnectionStrings.Reports.ODS Site
Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Why call a job from an automation tool from another automation tool which is more restricted? Seems like you just want to create additional problems.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

SQLTougherGuy (6/6/2016)
Hello,
I need to execute a bunch of Control-M Oracle jobs and packages using POWERSHELL. These jobs will be scheduled using SQL Agent. I have the following code (see below), and I am assuming that I can just plug this into a SQL Agent job? Can anyone assist?
Any help would be greatly appreciated.
powershell d:\util\ControlM\DEV\Tools\Wrapper.ps1 -ConfigPath %%configpath -Environment DEV -Country US -SqlFile D:\util\ControlM\DEV\ctrlm\assets\sql\SCRIPT.sql -ConnectionStringProperty ConnectionStrings.Reports.ODS Site

Presumably you have tried setting this up and it failed? What problems did you encounter?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lord Slaagh SSCertifiable Points: 5710 More actions · Answer 3

Luis - The command-line that I provided will be executed from SQL Agent. From what my boss has told me, re engineering these Oracle packages to SSIS is going to be "close to impossible", so the work around is to convert the Control-M jobs to SQL agent. So we are moving away from Control-M and using SQL Agent in it's place, since the packages, more or less, just execute PS scripts and SQL scripts.

Phil - I have not tried to execute this. I am seeing what options are. So, I guess my question is... Will the command line that I provided execute in SQL Agent as is, as a Powershell execution type?

Sorry for this guys. I am somewhat new to PS and I haven't used Oracle since 8i, so I am a man without a country :).

Thanks again for your quick responses.

Everyone has a plan until they get punched in the mouth. --Mike Tyson

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

SQLTougherGuy (6/6/2016)
Luis - The command-line that I provided will be executed from SQL Agent. From what my boss has told me, re engineering these Oracle packages to SSIS is going to be "close to impossible", so the work around is to convert the Control-M jobs to SQL agent. So we are moving away from Control-M and using SQL Agent in it's place, since the packages, more or less, just execute PS scripts and SQL scripts.
Phil - I have not tried to execute this. I am seeing what options are. So, I guess my question is... Will the command line that I provided execute in SQL Agent as is, as a Powershell execution type?
Sorry for this guys. I am somewhat new to PS and I haven't used Oracle since 8i, so I am a man without a country :).
Thanks again for your quick responses.

It should work. Powershell is an available Job Step Type in SQL Agent (link).

You may have to play around with the command line to get the syntax exactly right.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

So you're moving away from Control-M.

If I remember correctly, Control-M tasks are very similar to a cmdexec step on SQL Server Agent. You might need to check that the connections are correct and you have properly installed the Oracle Client.

You have to try something to see the problems you face.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lord Slaagh SSCertifiable Points: 5710 More actions · Answer 6

Thanks guys!

Everyone has a plan until they get punched in the mouth. --Mike Tyson