Running SSIS-packages in stored procedure using dtexec

Question

 

Hi,

 

I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.

 

The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.

 

Here is a sample list of ssis-packages from procedure:

 

exec xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\aaa.dtsx"'

exec xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\bbb.dtsx" '

exec xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\ccc.dtsx"'

exec xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\ddd.dtsx"'

exec xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\eee.dtsx"'

 

So, if eg bbb.dtsx would fail, is it possible to get the whole procedure failing?

 

Answer 1

It won't fail since xp_cmdshell insn't failing, it doesn't really care what command you're passing to xp_cmdshell, it could be dtxec, ipconfig, ping, winzip... Anything.

Maybe you need to revisit how you're designing your processes.

Answer 2

I don't recommend using this approach since the SQL Agent has the same workflow capabilities and xp_cmdshell is usually an audit violation, but if you want to fail the entire procedure if one package fails then this should do the trick.

begin tran

-- will succeed
declare @rc int
exec @rc = xp_cmdshell 'dir c:\'
select @rc

if ( @rc <> 0 )
    goto fail

-- will fail for me, no k drive
exec @rc = xp_cmdshell 'dir k:\'
select @rc

if ( @rc <> 0 )
    goto fail

commit

goto done

fail:
    print 'i failed'
    rollback

done:
    print 'all done'

Answer 3

Hi there,

 

a colleque of mine modified your code a bit and it look like this. Is this what you ment? Like this the procedure won't fail, unfortunately... What did I do wrong?

 

Here is the actual code:

 

declare @rc int

exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_general_ledger.dtsx"'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_amr_amrr_luokittelu.dtsx"' else print 'Error on SSIS_imp_text_general_ledger.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_amrr_vs_virkakoodi.dtsx"' else print 'Error on SSIS_imp_text_amr_amrr_luokittelu.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_activities.dtsx"' else print 'Error on SSIS_imp_text_amrr_vs_virkakoodi.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_appointment_visits.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_activities.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_exams.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_appointment_visits.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_M1_groups.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_exams.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_operations.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_M1_groups.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_10.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_operations.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_20.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_10.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_30.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_20.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_40.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_30.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_50.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_40.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_80.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_50.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_prodhier_90.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_80.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_supplies.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_prodhier_90.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_unit_activities.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_supplies.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_unit_M1_groups.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_unit_activities.dtsx'

if @rc = 0 exec @rc = xp_cmdshell 'dtexec /f "E:\SSIS\Sourcedata\Sourcedata\SSIS_imp_text_tb_ctrl_period_conversion.dtsx"' else print 'Error on SSIS_imp_text_tb_ctrl_unit_M1_groups.dtsx'

if @rc = 0 print 'Error on SSIS_imp_text_tb_ctrl_period_conversion.dtsx'

 

 

Answer 4

We recommend redesigning your approach.  The Sql Agent job approach mentioned earlier would be good; you could also chain your packages together using a series of execute package tasks and execute that package from your stored procedure.

 

As to the difficulty you're having with the stored procedure logic, this is pretty generic logic.  You'll probably have better luck getting that worked out (if you choose to continue with this approach) in a TSQL forum.