Feeds:
Posts
Comments

 

SELECT ss.last_run_outcome /*1-Success,0-Fail*/

 

FROM msdb.dbo.sysjobs s INNER JOIN msdb.dbo.sysjobservers ss

 

ON s.job_id = ss.job_id

 

WHERE s.name= ‘Job_Name’

 

Advertisements

CREATE
TABLE  tbl

(

ID 
INT IDENTITY(1,1),

NAME
VARCHAR(100)

)

GO

 

INSERT
tbl

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME2’

GO

 

 

WITH
CTE_Duplicate

AS

(

SELECT
Rank=ROW_NUMBER() OVER ( PARTITION BY
NAME ORDER
BY NAME),*

FROM
tbl

)

 

DELETE
FROM CTE_Duplicate

WHERE
Rank>1

GO

 

 

 

 

 

–Create table

CREATE
TABLE tbl

(

 Product_Name
VARCHAR(100),

 QTY
INT

)

GO

 

–Insert rows

INSERT
tbl

SELECT
‘prd1’,10

UNION ALL

SELECT
‘prd2’,20

UNION ALL

SELECT
‘prd3’,30

UNION ALL

SELECT
‘prd1’,40

UNION ALL

SELECT
‘prd2’,50

GO

 

SELECT
* FROM 
tbl

GO

 

 

–Dynamic Pivot

DECLARE
@PivotColumn VARCHAR(MAX)

SELECT
@PivotColumn =

                                                COALESCE(

                                                @PivotColumn
+ ‘,[‘ + cast(Product_Name as
varchar)
+ ‘]’,

                                                ‘[‘ +

                                                cast(Product_Name as varchar)+ ‘]’

                                                )

                                                FROM (

                                                                        SELECT DISTINCT
Product_Name FROM tbl)b

 

DECLARE
@PivotSQL NVARCHAR(MAX)

SET
@PivotSQL = N

                                                SELECT *

                                                FROM (

                                                SELECT

                                                Product_Name,QTY
from tbl

                                                ) AS

                                                PivotData

                                                PIVOT (

                                                SUM(QTY)

                                                FOR Product_Name

                                                IN (

                                                ‘ +

                                                @PivotColumn
+

                                                )

                                                ) AS

                                                PivotTable’

 

EXECUTE(@PivotSQL)

GO

 

 

Sometimes we don’t need static file name reason can be many, you already have a file with the same name in your target
folder and you don’t want to override or you can’t override it or maybe you want to keep track of all historical files or may be because of some xyz reasons.

Right click on Control Flow pane, choose variables, and then add three variables FilePath, FileName and FileExtension of data type String

1. FilePath-: Assign your directory path say C:\Output

2. FileName:-Select property pane of this variable and modify below two properties

EvaluateAsExpression=TRUE

Expression = "OutputFileName"+(DT_STR,4, 1252)  YEAR( GETDATE()  )+RIGHT("0" + (DT_STR, 2, 1252)  MONTH(GETDATE()  ), 2) + RIGHT("0" + (DT_STR, 2, 1252)  DAY( GETDATE() ), 2)

 

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

3. FileExtension: Assign extension value “.txt”



4. Next, Right-click the Flat File connection and choose Properties and set below two properties

DelayValidation=TRUE 

ConnectionString =Choose “Connection String” property and assign value of Expression like below

@[User::FilePath]+@[User::FileName]+ @[User::FileExtension]

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

Checkpoints in SSIS

What and why?

This feature enables us to re-run SSIS package from last point
of failure rather than running this package from beginning

How?

Enable checkpoint property of package, this will enable to save
execution history of tasks in a checkpoint file and this enables SSIS to
identify from where it need to restart package (last point of failure)

How to configure this
property?

Go to package properties (Right click on control flow tab and
choose properties), shown below

Configure to look like below

Step1 – CheckpointFileName – Already pointed above about this
checkpoint file, this file gets created only if package fails

Step2 – CheckpointUsage

·        
Never:
Do not look for checkpoint file even if the file exists

·        
Always:
always look for checkpoint file, this will result in error.

·        
IfExists: this
is the best option , look for checkpoint file if it exists

Step3 SaveCheckpoints – True, of course, we
want create check point file. If you set False no file will be created in Step1

 

Once you have followed above steps, you must set the FailPackageOnFailure property
to true for all the containers/tasks in the package to enable them to
be identified as restart point.

Note: Just to add
checkpoint can only be applied for the control flow tasks

 

 

 

 

Script all jobs

SELECT
* FROM  msdb.dbo.SysJobs

 

 

Script all job Steps

SELECT
* FROM msdb.dbo.SysJobSteps

 

To get all steps of one Job ,say ‘Job 1’

SELECT
SJ.name AS
JobName,JB.Step_Name,JB.Step_id FROM

msdb.dbo.SysJobSteps JB

INNER
JOIN msdb.dbo.SysJobs SJ

ON
JB.job_id=SJ.job_id

WHERE
SJ.name=‘Job1’

 

Output

JobName  

Step_Name 

Step_id

Job1

Step 1

1

Job1

Step 2

2

Job1

Step 3

3

 

As shown above,’Job1′ has three steps.

 

Execute this job

EXEC msdb..sp_start_job @job_name = ‘Job1’

 

To Execute this job to start from certain step

EXEC msdb..sp_start_job @job_name = ‘Job1’, @step_name=‘Step 2’

Above can be used in “Execute SQL Task” to call job/Job Step

 

To find a column name in entire database  and then generate SELECT
script on all those tables

 

SELECT

‘SELECT * FROM’+‘ ‘+NAME+‘ ‘

FROM
sys.tables

WHERE
object_id in

(

SELECT
object_id FROM
sys.columns

WHERE
NAME=‘ColumnName’

)

%d bloggers like this: