• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

create exception rows using ssis

Hi,
I have dimension table with identity row dim_id ,
I want to add exception rows in dimensions like
-99 invalid
-98 unknown

how can i do that using ssis .
0
sam2929
Asked:
sam2929
  • 3
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Before Loading the Dimension Table Execute below SQL in an Execute SQL Task :

IF NOT EXISTS ( SELECT 1 FROM Dim_1 WHERE DIM_ID = -99)
INSERT INTO DIM_1 VALUES (-99,'Invalid')
GO

IF NOT EXISTS ( SELECT 1 FROM Dim_1 WHERE DIM_ID = -98)
INSERT INTO DIM_1 VALUES (-98,'Unknown')
GO

and So on....
0
 
sam2929Author Commented:
Msg 8101, Level 16, State 1, Line 10
An explicit value for the identity column in table 'DIM_test' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
Pawan KumarDatabase ExpertCommented:
Are you inserting data in the identity column?

Is it necessary ? If yes then  you need to use set  IDENTITY_INSERT  ON
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
sam2929Author Commented:
yes I am doing that .

Quick question how you usually insert exception rows in dimensions can you please give example .
0
 
sam2929Author Commented:
This is what I want to do

CREATE TABLE [dbo].[DIM_sexcode](
      [DIM_SEXCODE_ID] [int] IDENTITY(1,1) NOT NULL,
      [Sexcode ] [nvarchar](255) NULL,
      [ Sexdescription] [nvarchar](255) NULL,
      [DateCreated] [datetime] NULL,
      [DataModified] [datetime] NULL
) ON [PRIMARY]

I want to insert data and two exception rows  --99 and -98 to link to fact table
0
 
Pawan KumarDatabase ExpertCommented:
Please use this pseudocode..

SET IDENTITY_INSERT [dbo].[DIM_sexcode] ON

INSERT [dbo].[DIM_sexcode] (DIM_SEXCODE_ID, Sexcode , Sexdescription, ...)
VALUES (.., ...)

SET IDENTITY_INSERT [dbo].[DIM_sexcode] OFF
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now