Incremental Load in ssis with example

Incremental Load in SSIS with example

This post demonstrates the incremental load in SSIS with example. Based on the business requirement and data volume we should determine the ETL process. We’ll assume we have a low to mid-sized source data file and here are the requirements:

Tech Tip : Carry out complex database operations and monitor them remotely by migrating your Sql server into the cloud and access it remotely on your preferred device(PC/Mac/android/iOS) with cloud hosted virtual desktop from:

Apps4Rent

CloudDesktopOnline with dedicated migration support from www.Apps4Rent.com

ETL Requirement:

We have a distributed database system and getting customer information on daily basis to load into SQL Server instance by following the below conditions:

  • Customer information is sending in flat file format
  • We are getting Delta Data Feed that means it includes new, updated and deleted customer’s data.
  • Compare Source (Flat File) and Destination (SQL Server) customer ID
  • If no match found at destination: Insert Row into Destination
  • If match found and IsDeleted = 1 / True: Delete the row from Destination
  • If match found: Update row from source to destination

Note:

  • We are using Sales.Customer from [AdventureWorks].
  • Be cautious in using OLEDB Command for large datasets as it’s a row by row process and impact the performance.
  • In case of doing incremental load for large datasets, load data into staging table and then update / delete using a query through Execute SQL Task.

 ETL Solution Design:

Flat File Source: To extract data from source flat file

Data Conversation: Convert source columns datatype to match the destination customer table

Lookup: Compare source and destination customerID column

  • No-Match Output: When there is no match found in destination
  • Match Output: When match found between source and destination

Conditional Split: Check if IsDeleted = 1 then redirect it to DELETE output. Redirect default output to UPDATE output.

OLEDB Destination: For inserting new records

OLEDB Command (Update): For updating rows

OLEDB Command (Delete): For deleting rows

ETL Package Design:

Open SQL Server Data Tools

Create a new Project name it as “Sales_ETL” and Package name it as “Customer_ETL”

Incremental Load in SSIS with example

Create two connection managers 1) Source – New Flat File Connection 2) New OLE DB Connection

Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example

Add a Data Flow Task to Control Flow and name it as “Customer_Data_ETL”

Incremental Load in SSIS with example

In Data Flow Task, add Flat File Source and name it as “Extract_Customer_From_FlatFile” and connect to the flat file source:

Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example

Add “Data Conversion” and convert datatype for all columns as per the destination table:

Incremental Load in SSIS with example

Add lookup transformation and configure as below

Full Cache: Sine we have a small reference data set we can choose Full Cache option. It means it capture the entire reference set into Memory instead of connecting database every time when required.

Redirect: Rows to No Match when no match found at destination.

Incremental Load in SSIS with example

Connections: Instead of getting entire table we are using a SQL Query as we required only CustomerID.

Incremental Load in SSIS with example

Column Mapping:

Here we are mapping source column “Copy of CustomerID” to destination column “CustomerID”.

To differentiate between source and reference make Output Alias as “Dst_CustomerID”.

Incremental Load in SSIS with example

Add a OLEDB Destination and name it as “Insert Customer”. Map No Match output from Lookup to OLEDB Destination:

Incremental Load in SSIS with example

Edit properties for OLEDB Destination “Insert Customer”, connect destination and configure properties and map columns:

Incremental Load in SSIS with example

Map columns from converted input columns to destination columns as below:

Incremental Load in SSIS with example Incremental Load in SSIS with example

ETL package is ready to handle new customer INSERT. Now we need to handle “Updated” & “Deleted” customers. Add Conditional Split transformation and connect “Lookup Match Output”:

Incremental Load in SSIS with example

Now we need to configure Conditional Split transformation to identify the Deleted & Updated rows:

Output Name: Name it as “Delete Customer” and apply condition isdeleted == “True”

Default Output Name: “Updated Customer” as remaining all rows comes in default output where isdeleted <> “true”

Incremental Load in SSIS with example

Now we need UPDATE and DELETE customer data in destination for that we are going to use “OLE DB Command”:

Incremental Load in SSIS with example

Configure OLEDB Command – Update Customer

Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example

Configure OLEDB Command – Delete Customer

Incremental Load in SSIS with example Incremental Load in SSIS with example Incremental Load in SSIS with example

Finally the package data flow looks like below:

Incremental Load in SSIS with example

ETL Package Execution:

Case 1: Customer Table is empty and Flat File is having 1702 rows

Incremental Load in SSIS with example

Case 2: Passing Delta data; 15 New Rows, 10 Updated Rows, 5 Rows with IsDeleted = 1;

Incremental Load in SSIS with example

Summary:

  • This post explains a way to implement incremental data load using SSIS
  • We used a small dataset to demonstrate the incremental load
  • Most important point to be noted: “OLEDB Command” is a row based operation and execute the SQL Statement for each row coming input row.

Windows Cloud

Posted in MSBI, SSIS | Tagged , , , , , , , , , , , , , | 6 Comments

Script all Primary Keys, Foreign Keys in a SQL Server using T-SQL

Script all Primary Keys, Foreign Keys in a SQL Server using T-SQL

Find the relationships between tables in sql server
Get Primary Key and Foreign Key details using T-SQL
 
How to find all related tables of a given table?
You got a new database and you started working on that. Essentially when times we have to work with a new database first we need to understand the table structure and relationships. The overall structure we can understand from schema / entity diagrams.
But when it is time to start developing sql code on new database it would always be an easier way to find dependent objects of a given table using “sp_help” or with “Alt+F1”.
Here with I am giving one more way which is handy to find out these details.

When we need to Script all Primary Keys and Foreign Keys in SQL Server:

  1. When we need to understand a database system
  2. Data Imports / Delete / Truncate
  3. While moving code between environment
Now we will see the script to list out all primary and foreign keys in sql server using t-sql

Have a look at the stored procedure “[usp_get_related_Tables]” below.

 

To know relationship (Primary key/ Foreign Key) details for all tables

IF EXISTS (SELECT 1 FROM sys.sysobjects WHERE TYPE='P' AND NAME='usp_get_related_Tables')


BEGIN 

DROP PROCEDURE [dbo].[usp_get_related_Tables];

END

GO


CREATE PROCEDURE [dbo].[usp_get_related_Tables] (
@tbl_Schema VARCHAR(50) = NULL,
@tbl_Name VARCHAR(100) = NULL)
AS
BEGIN

SELECT TC.CONSTRAINT_SCHEMA AS 'Table_Schema',
TC.TABLE_NAME AS 'Table_Name',
TC.CONSTRAINT_NAME AS 'PrimaryKey_Name',
CCU1.COLUMN_NAME AS 'PrimaryKey_Column',
COALESCE(RC.CONSTRAINT_NAME,'N/A')   AS 'ForeignKey_Name',

COALESCE(CCU2.Column_Name, 'N/A') AS 'ForeignKey_Column',

CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'

ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME END AS'ForeignKey_Table'

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER 
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU1 ON 
TC.TABLE_NAME = CCU1.TABLE_NAME 
AND 
TC.TABLE_SCHEMA = CCU1.TABLE_SCHEMA AND

 TC.CONSTRAINT_NAME = CCU1.CONSTRAINT_NAME
LEFT 
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON 

TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON

 TC2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

LEFT 
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON

RC.CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME

WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_SCHEMA=ISNULL(@tbl_Schema,TC.CONSTRAINT_SCHEMA) AND 

TC.TABLE_NAME = ISNULL(@tbl_Name,TC.TABLE_NAME )

ORDER BY TC.TABLE_NAME,

 TC.CONSTRAINT_NAME,

 RC.CONSTRAINT_NAME

END
EXEC [dbo].[usp_get_related_Tables]

To know relationship (Primary key/ Foreign Key) details for all tables under a given schema “Sales”
EXEC [dbo].[usp_get_related_Tables] @tbl_Schema='Sales'

To know relationship (Primary key/ Foreign Key) details for a given Table “Employee”
EXEC [dbo].[usp_get_related_Tables] @tbl_Name='Employee'

 
This Procedure was tested on SQL Server 2005, 2008 and on 2008R2.
 
 
Posted in SQL Development, SQL Scripts | Tagged , , , , , , | Leave a comment

Automate database restore procedure SQL Server

Automate database restore procedure SQL Server

Automate database restore sql server

Make sure that the stored procedure “[dbo].[DB_Restore]” is available at Target Instance master database.

 Connect to target server instance and open a new query window from SSMS

Why Database Restore Automation?

  • It reduce the manual efforts in gathering file details.
  • Mostly restores happens in cross environments in this case this procedure can handle the file locations and naming conventions.
  • We can see the complete error details and also it verify the space details before starting the actual operation.

Get the backup file location and database name to which the backup needs to be restored as and execute the stored procedure as below:

USE MASTER
GO
EXECUTE [DB_Restore]
		@DBName='Test',
		@BackupFile_Path='D:\SQLDBA\Test_bkp.bak'

 

Output:

Check database to make sure that it is online.

Stored Procedure Code: Restore Automation Procedure code:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[dbo].[DB_Restore]') IS NOT NULL
BEGIN
DROP PROC [dbo].[DB_Restore];
END
GO

/*******************************************************************/
-- Proc_Type Date_Modified Created_By Description 
-- Automation 19-Oct-2012 UDAY Initial
--
-- @DBName: Database Name to be restored
-- @BackupFile_Path: Fully qualified Backup file name
-- EXECUTE [DB_Restore] 'Test','C:\U_World\Shared\Test_bkp.bak'
/*******************************************************************/
CREATE PROCEDURE [dbo].[DB_Restore](
@DBName SYSNAME,
@BackupFile_Path VARCHAR(255))
AS 
BEGIN 
SET NOCOUNT ON
DECLARE @DBFilename VARCHAR(100),
@DBLogFilename VARCHAR(100),
@DBDataFile VARCHAR(100),
@v_strDBLogFile VARCHAR(100),
@ExecSQL NVARCHAR(1000),
@ExecSQL1 NVARCHAR(1000),
@MoveSQL NVARCHAR(4000),
@ReplaceFlag NVARCHAR(50),
@Temp NVARCHAR(1000),
@ListSQL NVARCHAR(4000),
@ServerVersion NVARCHAR(20),
@RestorePath VARCHAR(500)

BEGIN TRY

   /**** Update @ReplaceFlag depends on database existence****/
SET @ReplaceFlag = ''   

IF exists (select name from sys.databases where name = @DBName)
BEGIN
SET @ReplaceFlag = ', REPLACE'
END 


/*** Create table ##FILE_LIST to hold file infor from backup file ***/
SET @ListSQL = ''
SET @ListSQL = @ListSQL + 'IF (EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME = ''##FILE_LIST''))'
SET @ListSQL = @ListSQL + 'BEGIN'
SET @ListSQL = @ListSQL + '   DROP TABLE ##FILE_LIST '
SET @ListSQL = @ListSQL + 'END '

SET @ListSQL = @ListSQL + 'CREATE TABLE ##FILE_LIST ('
SET @ListSQL = @ListSQL + '   LogicalName VARCHAR(64),'
SET @ListSQL = @ListSQL + '   PhysicalName VARCHAR(130),'
SET @ListSQL = @ListSQL + '   [Type] VARCHAR(1),'
SET @ListSQL = @ListSQL + '   FileGroupName VARCHAR(64),'
SET @ListSQL = @ListSQL + '   Size DECIMAL(20, 0),'
SET @ListSQL = @ListSQL + '   MaxSize DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   FileID bigint,'
SET @ListSQL = @ListSQL + '   CreateLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   DropLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   UniqueID UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   ReadWriteLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   SourceBlockSize INT,'
SET @ListSQL = @ListSQL + '   filegroupid INT,'
SET @ListSQL = @ListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   differentialbaseLSN DECIMAL(25,0),'
SET @ListSQL = @ListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
SET @ListSQL = @ListSQL + '   isreadonly BIT,'
SET @ListSQL = @ListSQL + '   ispresent BIT'

SELECT @ServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

IF @ServerVersion LIKE '10.%' OR @ServerVersion LIKE '11.%' OR @ServerVersion LIKE '12.%'
BEGIN
SET @ListSQL = @ListSQL + ', TDEThumbpr DECIMAL'
END

SET @ListSQL = @ListSQL + ')'
EXEC (@ListSQL)
PRINT @BackupFile_Path;
INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile_Path + '''')
ALTER TABLE ##FILE_LIST ADD D_File VARCHAR(150)

/*** Get the default file location. It help us when database is not already exists ***/
SELECT TOP(1) @RestorePath =  physical_name FROM sys.master_files;

SET  @RestorePath = REPLACE(@RestorePath, RIGHT(@RestorePath, CHARINDEX('\', REVERSE(@RestorePath))-1),'')

/*** Capture information od data files - MDF, NDF and LDF ***/
/*** We have two cases 1. Database is already there is instance ***/
/*** 2. New database Restore (ELSE) ***/
IF EXISTS (SELECT 1 FROM SYS.SYSDATABASES WHERE NAME=@DBName)
BEGIN
DECLARE @DataFile VARCHAR(150),
@LogFile VARCHAR(150);

SELECT TOP 1 @DataFile = physical_name 
FROM sys.master_files 
WHERE Database_ID = DB_ID(@DBName) AND TYPE=0;

SELECT TOP 1 @LogFile = physical_name 
FROM sys.master_files 
WHERE Database_ID = DB_ID(@DBName) AND TYPE=1;

UPDATE ##FILE_LIST 
SET D_File = @DataFile 
WHERE TYPE='D' AND FileID=1;


UPDATE ##FILE_LIST 
SET D_File = LEFT(@DataFile,LEN(@DataFile)-4)+'_'+CONVERT(VARCHAR(5),FILEID)+'.ndf'
WHERE TYPE='D'AND FileID=1;


UPDATE ##FILE_LIST 
SET D_File =LEFT(@LogFile,LEN(@LogFile)-4)+'_'+CONVERT(VARCHAR(5),FILEID)+'_log.ldf'
WHERE TYPE='L';

END
ELSE 
BEGIN
UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'.mdf' 
WHERE TYPE='D'AND FileID=1;

UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'_'+CONVERT(VARCHAR(5),FILEID)+'.ndf'
WHERE TYPE='D'AND FileID=1


UPDATE ##FILE_LIST 
SET D_File=@RestorePath+@DBName+'_'+CONVERT(VARCHAR(5),FILEID)+'_log.ldf'
WHERE TYPE='L'

END

    /*** Loop through the all files mdf,ldf and ndf ****/
    /*** Form a string &quot;MOVE&quot; To MDF, MOVE LDF etc ***/
DECLARE CurFiles CURSOR FOR 
SELECT 'MOVE N''' + LogicalName + ''' TO N''' + D_File + '''' 
FROM ##FILE_LIST

SET @MoveSQL = ''

OPEN CurFiles 
FETCH NEXT FROM CurFiles into @Temp


WHILE @@Fetch_Status= 0
BEGIN
SET @MoveSQL = @MoveSQL + @Temp + ', '
FETCH NEXT FROM CurFiles into @Temp
END

CLOSE CurFiles
DEALLOCATE CurFiles

/*** Kill all connections to the database before restore ***/
PRINT 'Killing active connections to the database : ' + @DBName + CHAR(10)

SET @ExecSQL = ''
SELECT   @ExecSQL = @ExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM     master.dbo.sysprocesses
WHERE    DB_NAME(dbid) = @DBName AND DBID <> 0 AND spid <> @@spid
AND status <> 'background'
AND status IN ('runnable','sleeping')

EXEC (@ExecSQL)

/*** Restore Database ***/ 
PRINT 'Restoring ' + @DBName + '; database from ' + @BackupFile_Path + CHAR(10)

SET @ExecSQL = 'RESTORE DATABASE [' + @DBName + ']'
SET @ExecSQL = @ExecSQL + ' FROM DISK = ''' + @BackupFile_Path + ''''
SET @ExecSQL = @ExecSQL + ' WITH FILE = 1,'
SET @ExecSQL = @ExecSQL + @MoveSQL
SET @ExecSQL = @ExecSQL + ' NOREWIND, '
SET @ExecSQL = @ExecSQL + ' NOUNLOAD, STATS=10 '
SET @ExecSQL = @ExecSQL + @ReplaceFlag

PRINT '/**************************************************/'
PRINT '/****** Start Restore Operation *******************/'
PRINT '/**************************************************/'

EXEC (@ExecSQL)

PRINT CHAR(10)
PRINT '/**************************************************/'
PRINT '/****** Restore Operation Completed ***************/'
PRINT '/**************************************************/'

END TRY
BEGIN CATCH
PRINT 'Restore Failed with error' + ERROR_MESSAGE()

IF (EXISTS (SELECT 1 FROM TEMPDB..sysobjects WHERE NAME ='##FILE_LIST'))
BEGIN
DROP TABLE ##FILE_LIST
END
RETURN
END CATCH

/*** Drop the temp table  ***/
IF (EXISTS (SELECT 1 FROM TEMPDB..sysobjects WHERE NAME ='##FILE_LIST'))
BEGIN
   DROP TABLE ##FILE_LIST
END

PRINT CHAR(10)+'Database ' + @DBName +' Restored Successfully '
SET NOCOUNT OFF
END

restore_automation_sql-server

 

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | 4 Comments