May 13, 2015 at 6:13 am
When you run in a job it uses a service account to run the commands. Ensure your service account can access all the resources needed by your script.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 13, 2015 at 6:57 pm
Thanks for your reply Orlando
I created proxies and yes the account can access the folders and files.
I created proxy for cmd and ran the job from that account and created proxy for powershell and run job from there.
When I run the cmd by it self its working good. the problem is when i run through agent.
Thanks
May 13, 2015 at 8:00 pm
Can you script the entire job out and post it here?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 13, 2015 at 8:17 pm
Thanks for reply Orlando
Please find the script here
USE [msdb]
GO
/****** Object: Job [CaseMix_Automation] Script Date: 14/05/2015 12:15:10 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 14/05/2015 12:15:10 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CaseMix_Automation',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'EPWH\SatyaKo', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CaseMix Package] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaseMix Package',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\revenue\casemix\1_Generate Casemix_ExcelFiles.dtsx\"" /SERVER "\"SQL-DWH-DEV\DWH_DEV\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'bi_etl_genuser_proxy'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [powersehll] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'powersehll',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'powershell.exe -ExecutionPolicy ByPass -File "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract\Powershell\Casemix_New.ps1"
',
@flags=32,
@proxy_name=N'bi_etl_genuse_proxy_cmd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [powertest] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'powertest',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'# Create by Satya Konathala
# Purpose to Automate the process of running cgs_console.exe automatically
$cgs ="\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\3M ANDRG GROUPER v2013.1\cgs_console.exe"
$ip1 = "-input"
$ipt1 = "-input_template"
$ipt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\ar50in.dic"
$up1 = "-upload"
$upt1 = "-upload_template"
$upt2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\templates\chboiout.dic"
$err = "-error_log"
$usk1 = "-userkey1"
$usk2 = "8th Edn - 6.0x"
$usk3 = "8th Edn - 7.0"
$usk4 = "8th Edn - 5.1"
$usk5 = "8th Edn - 4.2"
$usk = $usk2,$usk3,$usk4,$usk5
$fileDirec = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract"
$FilesArchive = "\\epwh.ad\EPWORTH\data\Workgroups\Business Intelligence\CaseMix3MGroupingExtract\Archive"
$files1 = get-childitem $fileDirec\*.txt
foreach($file1 in $files1)
{
#[System.Windows.Forms.MessageBox]::Show("We are proceeding with next step.")
foreach($bkey in $usk)
{
$upfile = [io.path]::GetFileNameWithoutExtension($file1)
#write-host $upfile
$up2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Output\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).csv"
$err2 = "\\epwh.ad\EPWORTH\data\Workgroups\Power Pivot Testing\Grouper\Error\$upfile $bkey $(get-date -f yyyy-MM-dd-hhmmss).txt"
& "$cgs" $ip1 "$file1" $ipt1 "$ipt2" $up1 "$up2" $upt1 "$upt2" $err "$err2" $usk1 "$bkey"
}
#[System.Windows.Forms.MessageBox]::Show($file1)
move-item $File1 $FilesArchive
}
',
@database_name=N'master',
@flags=0,
@proxy_name=N'bi_etl_genuser_proxy_ps'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [cmdtest] Script Date: 14/05/2015 12:15:10 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'cmdtest',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'cmd.exe /c "\\SQL-DWH-DEV\STAGINGFILES\calltesting.bat"',
@flags=0,
@proxy_name=N'bi_etl_genuse_proxy_cmd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
May 13, 2015 at 8:27 pm
If you setup Step 6 the same as Step 5 where it is a call to PowerShell.exe passing a script location on disk instead of a PowerShell step type with inline code you'll have a better chance. You'll need a proxy setup for the OS Exec step type.
The PowerShell step type was improved in SQL2012 but still does not offer the same user experience as running from a stand alone PowerShell session.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply