SSIS - How To Insert Data Into a SQL Server Table with Identity Column

Scenario:

We have dbo.Customer table in our SQL Server database.The ID column is identity column. We need to load the data from Flat File Source but want to insert the values in ID column from the source instead of SSIS create identity values for ID column.
Fig 1. Flat File Source with Identity Values

Destination Table Definition:
CREATE TABLE dbo.Customer(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
ADDRESS VARCHAR(50),
Phone VARCHAR(12))

Solution:

We will be reading data from text file by using Flat File Source and then load the data into OLE DB Destination ( SQL Server Table).

Step 1:
Drag Flat File Source and create connection to text file as shown in Fig 2 and Fig 3.
 Fig 2- Create Flat File Connection in SQL Server Integration Services (SSIS)

Fig 3-Flat File Connection-Preview Data 

Step 2:
Drag OLE DB Destination to Data Flow Pane and connect Flat File Source to it.Configure as shown below. Check the check Box for Keep Identity as shown in #4.
Fig 4- Configure OLE DB Destination

Map the Source columns to the destination table columns.

Fig 5- OLE DB Destination Mapping


Final Output:
I have insert one more record  with ID=5 in our source file. Let's run our SQL Server Integration Services Package and check if all the records are inserted correctly.
 Fig 6- View records in dbo.Customer table 

As we can see that all the records with source IDs are inserted successfully.

SSIS- How To Convert Blank Into Null In SSIS

Scenario:

We are reading data from Flat File Source in our SQL Server Integration Services (SSIS) Package. Some of the values for Address column are coming as Blank. We want to convert those blank values to Null before loading into our SQL Server table.
Fig 1. Flat File Source with blank values 

Solution:
We will be using Derived Column Transformation in our SSIS Package to replace these blank values with Null.

Drag Derived Column Transformation to Data Flow Pane and connect the Flat File Source to it.
Add a new column DER_Address and write expression as shown below in fig 2.

(DT_STR,50,1252)(TRIM(Address) == "" ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : Address)

Fig 2- Derived Column Expression to replace blank with Null values

Let's execute our SQL Server Integration Services Package and see if the blank values are replaced with Null. Data viewer in Data Flow Task is used to display the data for test.

Fig 3. SSIS Data Viewer to display the data in Data Flow Task

How To Reset Identity Column Value in SQL Server Table - SQL Server / T-SQL Tutorial Part 43


Let's start with understanding the Identity property first and then we can proceed to step,how can we reset it? Create a SQL Server Table with Identity Column by using below script

USE TEST
GO
CREATE TABLE dbo.Person( ID INT IDENTITY(1,1), Name VARCHAR(10))

Let's insert below records by using below statements
INSERT INTO dbo.Person(name
VALUES ('Aamir')
INSERT INTO dbo.Person(Name)
VALUES ('Aamir Shahzad')


Only one record will be inserted and second record will fail as the size of string is more than the size of column data type
Fig 1-Insert records in SQL Server Table


Check the Identity Value of a Column:
To check the identity value of a column, we can use below statement
--Check the Identity Value of a Column
DBCC CHECKIDENT ('dbo.Person');

Fig 2- DBCC CHECKIDENT to see the Identity Value


Even the insert was failed for second record but the identity value is incremented. If we will insert next record, the identity value will be 3 for that as we can see in Fig 3.


Here are couple of ways to reset the identity value of a column after deleting data.

Reset Identity Value by Using TRUNCATE:
If we want to delete everything from table and table does not have any foreign Key relation, we can Truncate table and that will clean the data as well set the identity value
Fig 3-Use Truncate to clean table and reset Identity Value



Reset Identity Value By Using DBCC CHECKIDENT:
If the table is in relationship with any other table (Primary-Foreign Key) relationship, we will not be able to truncate the table. In that case we have to delete the values from table by using Delete statement and then set identity value.
DBCC CHECKIDENT ('dbo.Person', RESEED, 0);

 Fig 4. Delete data from Table and use DBCC CHECKIDENT to reseed identity value


Video Demo : How to reset Identity Column Value in SQL Server Table

TSQL- How To Find The Size Of a SQL Server Table

Here are the two ways to find the size of a SQL Server table

1-- Right Click on the table and then go to the Properties. Then Click on Storage Tab and you will be able to see the size of Table

Fig 1-SQL Server Table Size by using Table Properties

2- OR we can use  sp_spaceused system Stored Procedure to find the size of a SQL Server table.

EXEC sp_spaceused 'TableName'

Fig 2- Find the Size of Table by using sp_spaceused Stored Procedure

sp_spaceused Stored Procedure return the size of a table in KB, to get the Size in MB, we can divide with 1024.

8952/1024=8.742187 MB

SSIS - How To Execute Batch File By Using SSIS Package

Scenario:

We have created a batch file that copies all the files from Source folder to Destination folder. We want to execute this batch file in SQL Server Integration Services (SSIS) Package.

Solution:

To execute batch file from SQL Server Integration Services Package, we will be using Execute Process Task from Control Flow Items.

Step 1: 
Drag Execute Process Task to Control Flow Pane. Double click or Edit it by right click
Locate the path for your Executable, In my case the CMD.exe is present on C:\Windows\System32\cmd.exe.
In Argument , provide the path of your batch file as given below
/c C:\Users\ashahzad\Desktop\Mybatch.bat

Note:
 You can create variables to save paths for Executable and Arguments so on different machines you can change those values by using SSIS Configuration.

Final Step: 
After executing our SSIS Package, we can see that the batch file was executed successfully by SSIS Package and files were copiedd from Source to Destination Folder.


TSQL - How To Keep One Record From Duplicate Records and Remove All Of Others From a SQL Server Table

We have a SQL Server Table from which has duplicate records. We want to keep only one records from duplicate records and delete all others.

We can use Common Table Expressions ( CTE) with Row_Number() function to achieve this


Let's create a SQL Server table with duplicate records
USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer 
VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',28)
,(4,'Sukhjeet','Singh',28)

Write our CTE to Mark the duplicate records for Delete

;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Age,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName,Age
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
SELECT * FROM CTE
Delete Duplicate Records and keep only one copy
;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Age,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName,Age
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
DELETE FROM CTE WHERE Rn>1
As we can see, all the duplicate copies are deleted and only unique records are left.


TSQL - How To Delete All Duplicate Records From a SQL Server Table

Here are couple of ways to delete duplicate records from a SQL Server table.

--Prepare Sample Data
USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer 
VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',27)
,(4,'Sukhjeet','Singh',28)

Delete all duplicate records by using Common Table Expressions(CTE):

;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
       DELETE FROM 
      CTE WHERE EXISTS (SELECT 1 FROM cte t WHERE t.FirstName=cte.FirstName 
AND t.LastName=cte.LastName AND rn>1)

Delete all duplicate records by using Group By and Left Join:

DELETE FROM C
   FROM dbo.Customer C
         LEFT JOIN 
    ( 
   SELECT FirstName,
            LastName,
            COUNT(*) AS RecordCnt
    FROM   dbo.Customer
    GROUP  BY FirstName,
            LastName
    HAVING COUNT(*) > 1) DR
   ON DR.FirstName=C.FirstName
   AND DR.LastName=C.LastName
   WHERE DR.FirstName IS NOT NULL
   AND DR.LastName IS NOT NULL

TSQL - How to Find duplicate records in a SQL Server Table

A very common question asked to TSQL Developers is "How will you find duplicate records in a table?" and also as developer we encounter this situation all the time. Here are couple of ways to find the duplicate records in a SQL Server table.

In below example, we want to find all the record which are duplicate by FirstName and LastName.

USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer
 VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',27)
,(4,'Sukhjeet','Singh',28)

SELECT * FROM  dbo.Customer

Find duplicate records by using Group by:

SELECT FirstName,
            LastName,
            COUNT(*) AS RecordCnt
    FROM   dbo.Customer
    GROUP  BY FirstName,
            LastName
    HAVING COUNT(*) > 1


Find duplicate records by using Common Table Expressions(CTE)

;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
       SELECT *
    FROM   CTE
    WHERE  Rn > 1



TSQL - How To Check If Column Exists for SQL Table/ View in Database


On daily basis, we come across this scenario when we have to find out if a column exists for
a Table or View in Database. Specially if we are debugging SQL Server Reports/An application,
we take a column name or part of it and want to know that from which table this column is used.

We can query INFORMATION_SCHEMA.COLUMNS view to get this information.

USE Test
GO--Create Test Table
CREATE TABLE dbo.Test
  (
     ID      INT IDENTITY(1, 1),
     Name    VARCHAR(100),
     Address VARCHAR(100)
  )
GO
--Create Test View
CREATE VIEW dbo.vw_TestAS
  SELECT ID,
         NAME,
         Address
  FROM   dbo.Test 

--Check if any of the object( Table,View) has column name='Address'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='ADDRESS'

Or If you want to search with some part of column name

SELECT * FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME LIKE '%ADD%'

Fig 1: How to Check if Column Exists for a SQL Server Table Or View in SQL Server Database









Video Demo for Information_Schema.Columns System View


TSQL - How To Check When Object ( Table, Stored Procedure, Trigger etc) was Modified Last Time

Scenario: 

We as SQL Server developers send our DDL Scripts ( Add New Column, Drop some column from table, or Alter Stored Procedure) to DBA. After deployment we verify those changes and sometime it happens that the changes are not done. Maybe DBA has missed one of the script to run. If we can get the last modified date for object ( Table,Stored Procedure, View etc) that can confirm the changes are done on given date.


Solution: 

 We can use below query to find out last modified date for Object( Table, Function, Trigger, Stored Procedure etc)

SELECT name,
       create_date,
       modify_date FROM   sys.objects
ORDER  BY modify_date DESC

Or we can provide the name of object for which we want to know the last modified date.

SELECT name,
       create_date,
       modify_date FROM   sys.objects 
WHERE name='ObjectName'

Fig 1: Get the Last Modified DateTime for an Object in SQL Server 



Video Demo:
How to Check the last Modified Datetime of an Object in SQL Server Database