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: 5741
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
The are no problems, only solutions. --John Lennon

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: 183706 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: 246960 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lord Slaagh SSCertifiable Points: 5741 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.

The are no problems, only solutions. --John Lennon

Phil Parkin SSC Guru Points: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 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: 5741 More actions · Answer 6

Thanks guys!

The are no problems, only solutions. --John Lennon