June 6, 2016 at 9:26 am
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
June 6, 2016 at 9:47 am
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.
June 6, 2016 at 9:50 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 6, 2016 at 10:07 am
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
June 6, 2016 at 10:30 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 6, 2016 at 10:31 am
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.
June 6, 2016 at 10:34 am
Thanks guys!
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