File System Deployment of the SSIS Package

We can deploy the package using the file system. We will deploy the file on the integration server.

Open the Integration server.

     

Connect this server

                                             

Now click on the stored package

    

Right-click on the File System and create a folder in which we are going to deploy the package.

Here I am creating a folder named My First Package deployment

   

Click Ok.

Now Refresh the File System folder. We will get the newly created package.

   

  Right click on the folder.

         

And select Import package

     

     

Select Package location as file system and select the file path. Click on Open.

    

Click OK. This package is deployed on the Integration server successfully.

See below.

   

Now we can execute the package.

This package is used to move some file from the input folder to the archive folder.

See the archive folder before package execution.

     

Now running this package.

Right click on the package and click on the Run package.

  

Execute package utility window will be open.

       

         

Click on the Execute button.

The package will start executing

   

It completed successfully.

Now see the Archive folder.

    

Get the list of all succeeded and in progress jobs from past 2 days

Job execution history is stored on the system table sysjobhistory in msdb database.  With the help of run_status flag, we will get the list of failure or canceled jobs.

Status of the job execution:

·         0 = Failed

·         1 = Succeeded

·         2 = Retry

·         3 = Canceled

·         4 = In Progress

With the help of below sql script we will get the succeeded or in progress jobs

SELECT   j.[name] as [Job Name],           js.step_name,           jh.step_name,           jh.run_date,           jh.run_time,                                  case when jh.run_status=1 then 'Succeeded'                                  when jh.run_status=4 then 'In Progress' end [Run Status],          jh.message,            jh.server  FROM     msdb.dbo.sysjobhistory jh           INNER JOIN msdb.dbo.sysjobs j             ON jh.job_id = j.job_id           INNER JOIN msdb.dbo.sysjobsteps js             ON j.job_id = js.job_id            AND jh.step_id = js.step_id  WHERE    jh.run_status in( 1,4)          AND jh.run_date > replace(CONVERT(varchar, getdate()-2, 23),'-','')  ORDER BY jh.instance_id DESC

We will get result as below

  

Get the list of all failed or canceled jobs from past 15 days

Job executing history are stored on the system table sysjobhistory in msdb database.  With the help of run_status flag we will get the list of failure or canceled jobs.

Status of the job execution:

·         0 = Failed

·         1 = Succeeded

·         2 = Retry

·         3 = Canceled

·         4 = In Progress

With the help of below sql script we will get the failed or canceled jobs

SELECT   j.[name] as [Job Name],           js.step_name,           jh.step_name,           jh.run_date,            jh.run_time,           jh.message,            jh.server  FROM     msdb.dbo.sysjobhistory jh           INNER JOIN msdb.dbo.sysjobs j             ON jh.job_id = j.job_id           INNER JOIN msdb.dbo.sysjobsteps js             ON j.job_id = js.job_id            AND jh.step_id = js.step_id  WHERE    jh.run_status in( 0,3)          AND jh.run_date > replace(CONVERT(varchar, getdate()-15, 23),'-','')  ORDER BY jh.instance_id DESC

We will get the result as below.

Get the Active (enabled) SQL server job Agent jobs

When we are creating the SQL server agent job, it is stored on the sysjobs table in msdb. With the help of enabled flag, we will get active and disable job.

Below script is use to get the list of enabled job

select DISTINCT sj.name As [job name],sysc.name as [job category], sjc.step_name as [Step name],sjc.subsystem, sc.name as [schedule name] from sysjobs sj inner join syscategories sysc ON sysc.category_id=sj.category_id LEFT OUTER JOIN sysjobsteps sjs on sjs.job_id=sj.job_id LEFT OUTER JOIN sysjobsteps sjc ON SJ.job_id = sjc.job_id LEFT OUTER JOIN sysschedules sc on sc.schedule_id=sjc.step_id where sj.enabled=1

  

SQL Server job schedule information

When we schedule a SQL server agent job, all schedule information is stored on the system table sysschedules table in MSDB. 

With the help of below sql script we will get the all details about the job.

select DISTINCT sj.name As [job name],sysc.name as [job category], sjc.step_name as [Step name],sjc.subsystem, sc.name as [schedule name], sc.enabled,sc.freq_type, sc.freq_interval,sc.freq_subday_interval, sc.active_start_date,sc.active_end_date, sc.date_created,sc.date_modified from sysjobs sj inner join syscategories sysc ON sysc.category_id=sj.category_id LEFT OUTER JOIN sysjobsteps sjs on sjs.job_id=sj.job_id LEFT OUTER JOIN sysjobsteps sjc ON SJ.job_id = sjc.job_id LEFT OUTER JOIN sysschedules sc on sc.schedule_id=sjc.step_id

We will get below information

Enabled column

This column store the information either schedule is enabled or not of the value is 1 it means the schedule is active and if it is zero then this schedule is disabled.

Freq_Type Columns

How frequently a job runs for this schedule.

·         1 = One time only

·         4 = Daily

·         8 = Weekly

·         16 = Monthly

·         32 = Monthly, relative to freq_interval

·         64 = Runs when the SQL Server Agent service starts

·         128 = Runs when the computer is idle

Freq_subday_type Column

Units for the freq_subday_interval. The following are the possible values and their descriptions.

·         1 : At the specified time

·         2 : Seconds

·         4 : Minutes

·         8 : Hours

Freq_relative_interval Column

When freq_interval occurs in each month if freq_interval is 32 (monthly relative). Can be one of the following values:

·         0 = Freq_relative_interval is unused

·         1 = First

·         2 = Second

·         4 = Third

·         8 = Fourth

·         16 = Last

Freq_Type and freq_interval in details

Value of Freq_Type	Effect on freq_interval
1 (once)	freq_interval is unused (0)
4 (daily)	Every freq_interval days
8 (weekly)	freq_interval is one or more of the following:
	1 = Sunday
	2 = Monday
	4 = Tuesday
	8 = Wednesday
	16 = Thursday
	32 = Friday
	64 = Saturday
16 (monthly)	On the freq_interval day of the month
32 (monthly, relative)	freq_interval is one of the following:
	1 = Sunday
	2 = Monday
	3 = Tuesday
	4 = Wednesday
	5 = Thursday
	6 = Friday
	7 = Saturday
	8 = Day
	9 = Weekday
	10 = Weekend day
64 (starts when SQL Server Agent service starts)	freq_interval is unused (0)
128 (runs when the computer is idle)	freq_interval is unused (0)