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 .
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSIS

From novice to tech pro — start learning today.