Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check for table constraints using ssis packages Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 12:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:28 AM
Points: 16, Visits: 113
I am having 2 tables. source table and target table ie

create table tableA
(
id integer
,fname varchar(20)
,lname varchar(20)
)

And

create table tableB
(
id integer
,fname varchar(20)
,lname varchar(20) not null
)

Sample data:
TableA
1,'a','aa'
2,'b',null

So using packages when I am inserting data from tableA to tableB then package will fail for null records of tableA (fname) as fname cannot be null in destination table.

How to make sure that packages run fine and the bad data is stored in error_table. Meaning tableB will have only 1 record and 1 record should go to error_table and package should execute successfully.

Thanks
Post #1483149
Posted Tuesday, August 13, 2013 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 7:08 AM
Points: 124, Visits: 180
Hello,

There are a few possibilities, here would be the most obvious two:

1) Use a Conditional Split before your Insert to evaluate your NULLs and send them to Error_table

2) Use the Error Output of the Destination Component to forward the rows in errors to your Error_Table
Post #1483810
Posted Saturday, August 17, 2013 11:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:28 AM
Points: 16, Visits: 113
Thanks for the Reply:)
Post #1485559
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse