My requirement is to run SSIS package from SQL Server agent job, I have 3 SSIS packages 2 packages loading the data to SQL Table and 3rd package processing the cube. I have created a 2 SQL Server Agent Job, one to run the package which is loading the data to SQL table and another to process the cube. I have tried the below solution but no is working upto the expectation as I want
Solution 1:- Created a SQL Server Agent Job with 3 steps.
1st Step to execute one package.
2nd Step to execute another package.
3rd Step to execute the package which processing the cube.
Issue:- 1st and 2nd step executed properly but the 3rd one is not getting executed
Solution 2:- Created a SQL Server Agent Job 3 steps.
1st Step to execute one package.
2nd Step to execute another package.
3rd Step to process the cube directly using XML code.
Issue:- Again the cube is not processing
Solution 3:- Created a 2 SQL Server Agent Jobs, 1 which execute the package to load the data and 2nd to process the cube. Independently both works fine. To run one after another I did the below steps.
1st Step to execute one package.
2nd Step to execute another package.
3rd Step write the T-SQL Command to execute the Job which process the cube (processing using SSIS Package).
Use msdb;
Go
EXEC dbo.sp_start_job N'Job_Name' ;
GO
Issue:- again the cube job is not executing
Solution 4:- Created a job with 1 step
Step:- T-SQL Command to execute the packages T-SQL Command:-
Use msdb;
Go
EXEC dbo.sp_start_job N'Job1_Name' ;
GO
EXEC dbo.sp_start_job N'Job2_Name' ;
GO
Issue:- Both job executing but not into the order, Cube processing package starts before the data load package ends its execution.
SSAS Cube is in Tabular Model
Do anybody has another approach for it?
