SSIS: Package Design Standard Template – Nachi Hosakoti

This is a standard design steps to create an SSIS Project.

d1

There will be two types of packages in our design pattern:

  1. Master package
  2. Child package

Say for example: we have 3 packages:

  1. Master package – Run controller
  2. Child package – Package A
  3. Child package – Package B.

We have two SQL agent jobs –

  1. Run Package A
  2. Run Package B.

Run Package A job will execute the master package Run Controller and pass “A” as parameter and as a result it will execute the Package A. Run Package B job will execute the master package Run Controller and pass “B” as parameter and as a result it will execute the Package B.

Master package will have 3 main components:

  1. Execute SQL Task – which will execute a store proc to log the package start- exec dbo.LogPackageStart
    @BatchLogID = ?
    ,@PackageName = ?
    ,@ExecutionInstanceID = ?
    ,@MachineName = ?
    ,@UserName = ?
    ,@StartDatetime = ?
    ,@PackageVersionGUID = ?
    ,@VersionMajor = ?
    ,@VersionMinor = ?
    ,@VersionBuild = ?
    ,@VersionComment = ?
    ,@PackageGUID = ?
    ,@CreationDate = ?
    ,@CreatedBy = ?
    d2d2-2
  2. Execute Package Task – it will decide what package to call depending on the parameter passed. Precedence Constraint – @[$Project::DataSetType]==”A”. Parameter passing to the child package
    d4
  3. Execute SQL Task – which will execute a store proc to log the package end – exec dbo.LogPackageEnd
    @PackageLogID = ?
    ,@BatchLogID = ?
    ,@EndBatchAudit = ?
    d3
  4. Execute SQL Task (Event handler – OnError):  which will execute a store proc to log the package error – exec dbo.LogPackageError
    @PackageLogID = ?
    ,@BatchLogID = ?
    ,@SourceName = ?
    ,@SourceID = ?
    ,@ErrorCode = ?
    ,@ErrorDescription = ?
    ,@EndBatchAudit = ?
    d5
    d6

Database design for Package logging. Say for example the database name for package logging is SSIS_Log. SSIS_Log will have the following tables:

  1. BatchLog
  2. Package
  3. PackageVersion
  4. PackageErrorLog

BatchLog table script: 

CREATE TABLE [dbo].[BatchLog](
[BatchLogID] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NOT NULL DEFAULT (getdate()),
[EndDateTime] [datetime] NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED
(
[BatchLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Package table script: 

CREATE TABLE [dbo].[Package](
[PackageID] [int] IDENTITY(1,1) NOT NULL,
[PackageGUID] [uniqueidentifier] NOT NULL,
[PackageName] [varchar](255) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[CreatedBy] [varchar](255) NOT NULL,
[EnteredDateTime] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
[PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

PackageVersion table script: 

CREATE TABLE [dbo].[PackageVersion](
[PackageVersionID] [int] IDENTITY(1,1) NOT NULL,
[PackageVersionGUID] [uniqueidentifier] NOT NULL,
[PackageID] [int] NOT NULL,
[VersionMajor] [int] NOT NULL,
[VersionMinor] [int] NOT NULL,
[VersionBuild] [int] NOT NULL,
[VersionComment] [varchar](1000) NOT NULL,
[EnteredDateTime] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_PackageVersion] PRIMARY KEY CLUSTERED
(
[PackageVersionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

PackageErrorLog table script: 

CREATE TABLE [dbo].[PackageErrorLog](
[PackageErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[PackageLogID] [int] NOT NULL,
[SourceName] [varchar](64) NOT NULL,
[SourceID] [uniqueidentifier] NOT NULL,
[ErrorCode] [int] NULL,
[ErrorDescription] [varchar](2000) NULL,
[LogDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_PackageErrorLog] PRIMARY KEY CLUSTERED
(
[PackageErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

Store proc [LogPackageStart]:

CREATE PROCEDURE [dbo].[LogPackageStart]
( @BatchLogID int
,@PackageName varchar(255)
,@ExecutionInstanceID uniqueidentifier
,@MachineName varchar(64)
,@UserName varchar(64)
,@StartDatetime datetime
,@PackageVersionGUID uniqueidentifier
,@VersionMajor int
,@VersionMinor int
,@VersionBuild int
,@VersionComment varchar(1000)
,@PackageGUID uniqueidentifier
,@CreationDate datetime
,@CreatedBy varchar(255)
)

AS
BEGIN
SET NOCOUNT ON

DECLARE @PackageID int
,@PackageVersionID int
,@PackageLogID int
,@EndBatchAudit bit

/* Initialize Variables */
SELECT @EndBatchAudit = 0

/* Get Package Metadata ID */
IF NOT EXISTS (SELECT 1 FROM dbo.Package WHERE PackageGUID = @PackageGUID AND PackageName = @PackageName)
Begin
INSERT INTO dbo.Package (PackageGUID, PackageName, CreationDate, CreatedBy)
VALUES (@PackageGUID, @PackageName, @CreationDate, @CreatedBy)
End

SELECT @PackageID = PackageID
FROM dbo.Package
WHERE PackageGUID = @PackageGUID
AND PackageName = @PackageName

/* Get Package Version MetaData ID */
IF NOT EXISTS (SELECT 1 FROM dbo.PackageVersion WHERE PackageVersionGUID = @PackageVersionGUID)
Begin
INSERT INTO dbo.PackageVersion (PackageID, PackageVersionGUID, VersionMajor, VersionMinor, VersionBuild, VersionComment)
VALUES (@PackageID, @PackageVersionGUID, @VersionMajor, @VersionMinor, @VersionBuild, @VersionComment)
End
SELECT @PackageVersionID = PackageVersionID
FROM dbo.PackageVersion
WHERE PackageVersionGUID = @PackageVersionGUID

/* Get BatchLogID */
IF ISNULL(@BatchLogID,0) = 0
Begin
INSERT INTO dbo.BatchLog (StartDatetime, [Status])
VALUES (@StartDatetime, ‘R’)
SELECT @BatchLogID = SCOPE_IDENTITY()
SELECT @EndBatchAudit = 1
End

/* Create PackageLog Record */
INSERT INTO dbo.PackageLog (BatchLogID, PackageVersionID, ExecutionInstanceID, MachineName, UserName, StartDatetime, [Status])
VALUES(@BatchLogID, @PackageVersionID, @ExecutionInstanceID, @MachineName, @UserName, @StartDatetime, ‘R’)

SELECT @PackageLogID = SCOPE_IDENTITY()

SELECT @BatchLogID as BatchLogID, @PackageLogID as PackageLogID, @EndBatchAudit as EndBatchAudit

END

Store proc [LogPackageEnd]:

CREATE PROCEDURE [dbo].[LogPackageEnd]
( @PackageLogID int
,@BatchLogID int
,@EndBatchAudit bit
)

AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.PackageLog
SET Status = ‘S’
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID

IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = ‘S’
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END

Store proc [LogPackageError]:

ALTER PROCEDURE [dbo].[LogPackageError]
( @PackageLogID int
,@BatchLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@ErrorCode int
,@ErrorDescription varchar(2000)
,@EndBatchAudit bit
)

AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.PackageErrorLog (PackageLogID, SourceName, SourceID, ErrorCode, ErrorDescription, LogDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, @ErrorCode, @ErrorDescription, getdate())

UPDATE dbo.PackageLog
SET Status = ‘F’
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID

IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = ‘F’
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s