How to execute a job step based on outcome of previous SQL Agent job step?

There was a requirement to stop a SQL Server Agent job if any of the Staging Tables are not loaded in to Data warehouse


And to keep it simple and perform this action - I have added a step in SQL Server Agent Job to check Audit table to ensure all the Staging tables are loaded before loading them in to Data warehouse


  DECLARE @dbState INT;

SELECT @dbState = RowCountCheck FROM [StagingDatabase].[vwEtlRowCountCheck] 

WHERE RowCountCheck = 0

--AND TableName NOT IN ('Audit_Log', 'Config','CHARGES_DUE_PER_ASSET')

;

IF @dbState = 0

BEGIN

  RAISERROR('All Staging tables are not loaded correctly', 11, 1);

END

Comments

Post a Comment

Popular posts from this blog

Request failed with HTTP status 503 Server Error

Image
Problem: When 100's of users ae trying to access Reporting services report then, SSRS throws error as below : "Request failed with HTTP status 503 Server Error" and how to resolve this error Solution: From experience, this issue can be caused by multiple reasons Problem might be related to: 1 - TCP/IP Port Exhaustion: When a client initiates a TCP/IP socket connection to a server, the client typically connects to a specific port on the server and requests that the server respond to the client over a short-lived TCP or UDP port. The default range of short-lived ports used by client applications is 1025 through 5000. Under certain conditions, it is possible that the available ports in the default range are exhausted. 2 - If you are using any custom assembly method in any of your report (say for example PrivateFontCollection class or anything else), if yes then you might want to review that 3 - Change MaxActiveReqForOneUser from its default value o...
Read more

Removing and Manipulating Time portion from DateTime in SQLServer

Image
One of the common transformation we would have to do as part of ETL would be manipulating Time portion. Please see some of below code, which would help to resolve some of the time related transformation Case1 - Getting only Date portion from DateTime /* Removing Time portion from DateTime */   Select -- Standard date with time   getdate () As DateWithTime , -- Returns Date Portion Only   dateadd ( d , 0 , datediff ( d , 0 , getdate ())) As DateOnly Result as below: Case2 -  /* Removing Seconds from DataTime */ /* Removing Seconds from DataTime */ declare @t datetime set @T = GETDATE () SELECT @T as Original , DATEadd ( ss ,- datepart ( ss , @t ), @t ) as wihtout_sec -- if no milisecs , DATEADD ( mi , DateDiff ( mi , 0 , @t ), 0 ) as OneMore_Variation Case3 - We want to Keep Hours but remove Minutes and Seconds from DateTime /* Keep Hou...
Read more

How to Troubleshoot Subscription issue in Reporting Services

Tips on troubleshooting Subscription issue in Reporting Services 1 – Look at the Reporting Services Log File Error Message from Log File: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed.  Success: False, Status: Failure writing file Startup :  The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup, .......      2 - Run below query agains...
Read more