I am trying to load data into my DimEmployee table using the Data Flow Task. However, I noticed when I run the SSIS, the DimEmployee filled with 4 records out of 9 and also the EmpKey starts counting from -1 then generates random numbers after that! Also I have a warning associated when running the package:
Warning 1 Validation warning. Data Flow Task: {DC10B0CA-A6DE-4036-98A6-496811C09D4A}: Truncation may occur due to inserting data from data flow column "TitleOfCourtesy" with a length of 25 to database column "Title" with a length of 8. DimEmployee.dtsx 0 0
I created a procedure in my SSMS which supposes to fill the nulls in the columns by 'UNKNOWN'
USE [NorthWindDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Insert_New_Employee]
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS
(
SELECT * FROM [dbo].DimEmployee
WHERE EmpKey = -1
)
BEGIN
SET IDENTITY_INSERT [dbo].[DimEmployee] ON
INSERT [dbo].[DimEmployee] (EmpKey, EmpId,Title, EmpFirstName, EmpLastName, HireDate,Country, StoreCode, Telephone,JobTitle) VALUES
(-1,-1,'UNKNOWN','UNKNOWN','UNKNOWN','1900-01-01', 'UNKNOWN','UNKNOWN', 'UNKNOWN','UNKNOWN');
SET IDENTITY_INSERT [dbo].[DimEmployee] OFF
END
END
GO
This is my DimEmployee Table
Create Table [dbo].[DimEmployee]
(EmpKey INT IDENTITY NOT NULL,
EmpId INT NOT NULL,
EmpFirstName NVARCHAR(50) NOT NULL,
EmpLastName NVARCHAR(50) NOT NULL,
Title NVARCHAR(8) NOT NULL,
HireDate DATE NOT NULL,
Country NVARCHAR(50) NOT NULL,
StoreCity NVARCHAR(50) NOT NULL,
Telephone NVARCHAR(25) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL);
Go
