How to Tracking the Execution of the Package

SELECT
(select convert(BIGINT, format(getdate(), 'yyyyMMddHHmmssf'))) as SessionID
,sysdatetime() as CurrentDate
,null ProcessStepSuccessMessage
,null ProcessStepErrorMessage
,null ObjectName
-----------------------------------

SELECT
'Load Gems Entity data into Staging' as ProcessStepName
,'Loading Gems Entity data into Staging failed' as ProcessStepErrorMessage
,'Loading Gems Entity data into Staging completed successfully' as ProcessStepSuccessMessage

-----------------------------------
EXECUTE  TaxStage.[RECON].[prProcessExecution]  ?,?,?,?,0,0,0,0,'In-Progress',null,null,null,? OUTPUT
-------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [RECON].[prProcessExecution]
@SessionID bigint,
@ProcessStepName nvarchar(255),
@EffectiveStartDate datetime,
@EffectiveEndDate datetime,
@ExpectedRecordCount bigint,
@ActualRecordCount bigint,
@ExpectedAmountTotal numeric(25,7),
@ActualAmountTotal numeric(25,7),
@ProcessStepStatus nvarchar(255),
@ProcessLogMessage nvarchar(2048),
@ProcessErrorMessage nvarchar(2048),
@ObjectName nvarchar(512),
@ProcessExecutionID bigint OUT
--@RecordsProcessed int OUT
AS
declare @ProcTS datetime, @ProcessStepID bigint, @ProcessID bigint
declare @ProcessExecutionCount int, @ProcessStepCount int, @SessionCount int
SELECT @ProcTS = getdate(), @ProcessExecutionCount = 0, @ProcessStepCount = -1
-- Get Process Step ID
SELECT @ProcessStepID = max(ProcessStepID) from TaxStage.RECON.ProcessStep
where ProcessStepName = @ProcessStepName
-- Get Process ID
SELECT @ProcessID = max(ProcessID) from TaxStage.RECON.ProcessStep
where ProcessStepID = @ProcessStepID
select @SessionCount = count(*)  from TaxStage.RECON.[Session] where SessionID = @SessionID
-- IF new session, then insert into Session table
IF @SessionCount = 0
BEGIN
INSERT INTO TaxStage.RECON.[Session]
SELECT top 1 @SessionID, @ProcessID, @ProcTS, NULL, 'In-Progress', @ProcTS, user, @ProcTS from TaxStage.RECON.[Session]
END
-- MERGE into Process Execution Table
MERGE TaxStage.RECON.ProcessExecution as pe
USING (
select top 1
@SessionID AS SessionID
,@ProcessStepID AS      ProcessStepID
,@ProcTS AS      StartTime
,@ProcTS AS      EndTime
,@EffectiveStartDate AS      EffectiveStartDate
,@EffectiveEndDate AS      EffectiveEndDate
,@ExpectedRecordCount AS      ExpectedRecordCount
,@ActualRecordCount AS      ActualRecordCount
,@ExpectedAmountTotal AS      ExpectedAmountTotal
,@ActualAmountTotal AS      ActualAmountTotal
,@ProcessStepStatus AS      ProcessStepStatus
,@ProcessLogMessage AS      ProcessLogMessage
,@ProcessErrorMessage AS      ProcessErrorMessage
,@ObjectName AS ObjectName
  from RECON.ProcessExecution
  where 1 = 1
) as stg
ON pe.SessionID = stg.SessionID
AND pe.ProcessStepID = stg.ProcessStepID
WHEN MATCHED THEN UPDATE SET
pe.[EndTime] = stg.[EndTime],
pe.[ActualRecordCount] = stg.[ActualRecordCount],
pe.[ActualAmountTotal] = stg.[ActualAmountTotal],
pe.ProcessStepStatus = stg.ProcessStepStatus,
pe.[ProcessLogMessage] = stg.[ProcessLogMessage],
pe.[ProcessErrorMessage] = stg.[ProcessErrorMessage],
pe.ObjectName = stg.ObjectName,
pe.LastUpdateTs = @ProcTS,
pe.EffectiveStartDate = stg.EffectiveStartDate,
pe.EffectiveEndDate = stg.EffectiveEndDate
WHEN NOT MATCHED THEN
INSERT(
[SessionID]
      ,[ProcessStepID]
      ,[StartTime]
      ,[EndTime]
      ,[EffectiveStartDate]
      ,[EffectiveEndDate]
      ,[ExpectedRecordCount]
      ,[ActualRecordCount]
      ,[ExpectedAmountTotal]
      ,[ActualAmountTotal]
      ,ProcessStepStatus
      ,[ProcessLogMessage]
      ,[ProcessErrorMessage]
 ,ObjectName
      ,[CreateTs]
      ,[LastUpdateUserID]
      ,[LastUpdateTs]
)
VALUES(
@SessionID
,@ProcessStepID
,@ProcTS
,@ProcTS
,@EffectiveStartDate
,@EffectiveEndDate
,@ExpectedRecordCount
,@ActualRecordCount
,@ExpectedAmountTotal
,@ActualAmountTotal
,@ProcessStepStatus
,@ProcessLogMessage
,@ProcessErrorMessage
,@ObjectName
,@ProcTS
,user
,@ProcTS
);
-- Derive Process Status for Session Table
SELECT @ProcessStepCount = COUNT(*) FROM RECON.ProcessStep where ActiveFlg = 'Y' and ProcessID = @ProcessID
SELECT @ProcessExecutionCount = COUNT(*) FROM RECON.ProcessExecution where SessionID = @SessionID
and ProcessStepStatus = 'Success'
IF @ProcessStepCount = @ProcessExecutionCount OR @ProcessStepStatus = 'Fail'
BEGIN
UPDATE TaxStage.RECON.[Session]
SET ProcessStatus = @ProcessStepStatus,  [LastUpdateUserID] = user ,[LastUpdateTs] = @ProcTS, EndTime = @ProcTS
WHERE SessionID = @SessionID
END
ELSE
BEGIN
UPDATE TaxStage.RECON.[Session]
SET ProcessStatus = 'In-Progress',  [LastUpdateUserID] = user ,[LastUpdateTs] = @ProcTS
WHERE SessionID = @SessionID
END
-- Return Process Execution ID
SELECT @ProcessExecutionID = max(ProcessExecutionID) from TaxStage.RECON.ProcessExecution
where ProcessStepID = @ProcessStepID
and   SessionID = @SessionID

-----------------------------------------------------

EXECUTE  TaxStage.[RECON].[prProcessExecution]  ?,?,?,?,0,?,0,0,'Success',?,null,null,? OUTPUT

How to read data from multiple Excel worksheets with SQL Server Integration Services

https://www.mssqltips.com/sqlservertip/4157/how-to-read-data-from-multiple-excel-worksheets-with-sql-server-integration-services/

How to Get Date Like 2016092210(yyyymmddhh) by using Expression Task

SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE((DT_WSTR, 30) GETDATE(),"-",""),":",""),".","")," ",""),1,14)

INTRODUCTION TO SSIS

Sql Server Integration Services (SSIS) is ETL (Extract, Transform, Load) Tool, developed by Microsoft to perform ETL operations; i.e. Extract, Transform, and consolidate data from different sources and load it to a single or multiple destinations. It provides a platform to build data integration and workflow applications. SSIS packages can be created using BIDS (Business Intelligence Development Studio).

Business Intelligence Development Studio is the environment that we will use to develop packages for data extraction, transformation, and loading (ETL) in Integration Services. Business Intelligence Development Studio is the Visual Studio 2008 environment with enhancements that are specific to business intelligence solutions. Business Intelligence Development Studio (BIDS) will be available as client tool when installing SQL Server Management Studio (SSMS).

Usage of SSIS:

Populate data warehouses.

• Manage, Clean and Standardize data.

• Merge data from multiple data sources, either from relational data bases or from flat files etc.

• Automating database administrative tasks.

SSIS Architecture

Microsoft SQL Server Integration Services (SSIS) consist of four key parts:

• SSIS Service
• SSIS Object Model
• SSIS runtime engine and the runtime executables
• SSIS dataflow engine 

Integration Services Service:

• Monitors running Integration Services packages and manages the storage of packages
• Integration Services object model
• Includes native and managed application programming interfaces (API) for accessing
• Integration Services tools, command-line utilities, and custom applications

Data Transformation Run-Time Engine:

•      Handles control flow of the package   
•      Support Logging,Debugging ,Config,Connections & transactions.
•      Package,containers,Tasks and Event handles.

Data Flow Engine(ETL):

•    Also called as Data Transformation Pipe Line Engine
•    Data sources or Data Adapters 
•    Transformations 
•    Destination Targets or Destination Adapters 

Please check below SSIS Architecture Diagram