SSIS Package Not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Package Not working

Author

Topic

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-04 : 02:30:50

We have developed SSIS package in which all the tasks have been connected using "Completion" Precedence Constraint. In works fine from BIDS even if one of the task is failed. But when scheduled from SQL Server Agent, if one of the task is failed it doesn't execute next task.

And If none of the task is failed, then all the tasks get executed successfully.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-04 : 10:22:23

make this change
under Job steps->advanced->on failure action->go to next step

Javeed Ahmed

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-04 : 19:35:01

quote:
Originally posted by ahmeds08

make this change
under Job steps->advanced->on failure action->go to next step

Javeed Ahmed

That's not what he means. There's just one agent step the runs the package. The package has several tasks and not all execute.

Check the SQL agent job history.

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-05 : 00:24:12

Yes I don't mean that. gbritton is right. The package has several tasks and not all execute of one of the task is failed. I checked the history, it only gives details about the failure tasks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-05 : 10:18:39

OK, then, look at the last task that failed in the job history and double-check it in BIDS. Is the precedence after it set to 'Completion'? (Note that the default is Success')

Try to run the package with DTEXECUI from the location specified in the SQL Agent job and observe the results. Also, double-check that the BIDS package is exactly the same one the agent job runs (e.g. the exact same location)

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-10 : 07:47:29

Through BIDS and DTEXEC it works fine. I have now enabled the log and it seems that Execute SQL Task was executed but it didn't take any time to execute. Job started at 5:44:42 AM and ended at 5:44:42 AM. It logged the event Preexecute and PostExecute both at the same time 5:44:42 AM but data was not updated through the SP but no Error event logged. Again when I ran SP anually it worked fine and updated data. SP Takes somewhere around 2 mins to execute.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 08:05:15

There's something wrong with your agent job definition. double-check it. If you like, script it (right-click the job, select script as... from the dropdown) and paste it here.

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-10 : 09:59:19

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/10/2014 23:54:36 ******/
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'SBS BI Data Refresh',
@enabled=1,
@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'SBSMS\SSISExecutor', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Kick start BI Data Refresh Process] Script Date: 09/10/2014 23:54:36

******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Kick start BI Data Refresh

Process',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/FILE "\\sydw8023\Cognospackages\Refresh Oracle Data.dtsx" /X86

/CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0,
@proxy_name=N'SSISExecutor'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run batch] Script Date: 09/10/2014 23:54:36 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run batch',
@step_id=2,
@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'Schtasks /run /tn "Cognos Cube Daily Refresh" /s sydvmw8044',
@flags=0,
@proxy_name=N'SSISExecutor'
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_jobschedule @job_id=@jobId, @name=N'Daily BI Data Refresh',
@enabled=1,
@freq_type=8,
@freq_interval=126,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130130,
@active_end_date=99991231,
@active_start_time=21500,
@active_end_time=235959,
@schedule_uid=N'a4553f84-6dce-4e34-91ea-0f1c2c94b618'
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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 10:52:48

OK, now be sure that user

SSISExecutor

can access the file

\\sydw8023\Cognospackages\Refresh Oracle Data.dtsx

(login as SSISExecutor) and try to open the file.

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-10 : 11:46:52

It can access that file becuase it is master package which runs one child package and In that child package there are Execute SQL task and some other tasks. If all the tasks are successfull then Execute SQL task also runs successfully. But if any of the task is failed then in that case Execute SQL task causes issue as mentioned earlier.

The Execute SQL task executes SP which takes approx 2 mins to finish. If any of other task is failed then Execute SQL task simply log Pre and Post Execute event and no error event. For that matter we have included "onError" event also while logging. It means that Execute SQL tasks runs successfully but SP within that fails to execute. Also after failuer if SP is run manually then executes successfully.

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-10 : 11:48:30

83477311 OnPreExecute SYDW8023 SBSMS\SSISExecutor Execute SQL Task 9C50C3E1-C4DA-4B68-AEE0-87052DFFE847 87F732C3-A1D8-4BA8-8A7B-95E934DA5633 8/27/14 5:44:42AM 8/27/14 5:44:42AM

83477312 OnPostExecute SYDW8023 SBSMS\SSISExecutor Execute SQL Task 9C50C3E1-C4DA-4B68-AEE0-87052DFFE847 87F732C3-A1D8-4BA8-8A7B-95E934DA5633 8/27/14 5:44:42AM 8/27/14 5:44:42AM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 12:14:41

Does the exec SQL task have an expression to conditionally disable it?

NileshMisal2014
Starting Member

7 Posts

Posted - 2014-09-11 : 05:38:05

No, there is no condition at all.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 15:22:38

If you're comfortable with it, find the Execute SQL task in the package (that is , the .dtsx file, which is just xml) and post it here. There's something in there that is changing its behavior when executed from agent.

Oh, have you deployed the package to msdb? It would be interesting to see what happens if you execute it from there, or using this method:

http://www.databasejournal.com/features/mssql/executing-a-ssis-package-from-stored-procedure-in-sql-server.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources