Replace Nulls with column defaults while inserting
-
17 มิถุนายน 2552 13:45Hi All,
I want SSIS to replace nulls from my input result to the column defaults at my destination. Is this possible?
That is, my destination table has column definitions like:
Column1 varchar(256) default 'def1'
Column2 varchar(256) default 'def2'
But my input stream has null in Column1 and Column2. Currently I am using a derived column with expression like IsNull( [Column1] ) ? 'def1' [Column1]. Is there a more direct way?
Thanks,
Syed Mehroz Alam
My Blog | My Articles
ตอบทั้งหมด
-
17 มิถุนายน 2552 15:48 The only better way is to use ISNULL in your select statement. There's almost no impact on the source server and the derived column tranform can be dropped.
Of course there are probably twenty other ways to accomplish the same end, but that's just the way SSIS is.- เสนอเป็นคำตอบโดย Todd McDermidMVP, Moderator 17 มิถุนายน 2552 16:11
-
18 มิถุนายน 2552 5:05Thanks for the reply but how can I make IsNull to replace nulls with the default from my destination column.
For example, if we omit columns from Insert Into Table (Columns, ... ) statment then SQL server automatically inserts the default values of the omitted columns. Do we have something similar is SSIS where we can ignore the column values where the input stream contain nulls?
Regards,
Syed Mehroz Alam
My Blog | My Articles -
18 มิถุนายน 2552 8:36Hi,
I don't think there is anything like replacing nulls as you are saying in SSIS.
you can do wht GarthH_BI siad.
Thanks
Rohit -
18 มิถุนายน 2552 8:50
Thanks for the reply but how can I make IsNull to replace nulls with the default from my destination column.
For example, if we omit columns from Insert Into Table (Columns, ... ) statment then SQL server automatically inserts the default values of the omitted columns. Do we have something similar is SSIS where we can ignore the column values where the input stream contain nulls?
Regards,
Syed Mehroz Alam
My Blog | My Articles
Yeah..if you want to insert default values when the value in the column is NULL......
You can use a derived Column Transformation and write a expression like
ISNULL( [Col_Name] ) ? "Deafult" : [ColName]
This will check if Column has null values or not and if yes- it will insert Deafult else it will retain the Column value
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ -
18 มิถุนายน 2552 8:59You are right but you have hardcoded "Deafult" in your following expression:
ISNULL( [Col_Name] ) ? "Deafult" : [ColName]
My question was how to use the value from the default constraint of the destination column.
Hope that makes sense.
Syed Mehroz Alam
My Blog | My Articles -
18 มิถุนายน 2552 9:08I doubt if we can read the default constraint in SSIS from sql server
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ -
18 มิถุนายน 2552 9:18Of course this may not be possible easily, but notice that while mapping input stream to destination columns in an OleDB Destination, if we set any destination column with "ignore" mapping, then the default constraint is applied for that column. I was wondering if there is an standard way to use the same for the null columns from input stream. But looking at the discussion so far, it seems we do not have any direct easier method. Let me have this thread open for a couple of days more hoping to get a nice idea for this scenario.
Regards,
Syed Mehroz Alam
My Blog | My Articles -
18 มิถุนายน 2552 11:29hold on a sec... if your incoming data is null, then destination column will automatically take default defined for the destination column. you just need to have check constraint ON on destination component.
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ -
18 มิถุนายน 2552 12:23Try this
INSERT <Table Name>
DEFAULT VALUES
This will insert :
1. Default Values for all columns where Defaults are specified
2. NULL for all nullable columns where no default is specifed.
This will fail if there is no default set a NOT NULL column.
Instead if you want to insert default value to a column
INSERT <TableName>
col 1
col 2
values
val1,
DEFAULT
This will work in the following way:
1. If Col2 - has a default value as val3 then val1, val3 will be iserted as a record.
2. If col2 - does not have a default value and is nullable - val1, NULL will be inserted.
The query will fail if the column has no default and is NOT NULLable.
Hope this answers.
Karteek -
18 มิถุนายน 2552 13:44
hold on a sec... if your incoming data is null, then destination column will automatically take default defined for the destination column. you just need to have check constraint ON on destination component.
Its is not, I get the following error:
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
[OLE DB Destination [16]] Error: There was an error with input column "MaritalStatus" (107) on input "OLE DB Destination Input" (29). The column status returned was: "The value violated the integrity constraints for the column.".
@Karteek,
I was not able to get you here. Please note that I am using SSIS dataflow task to insert records.
Regards,
Syed Mehroz Alam
My Blog | My Articles -
19 มิถุนายน 2552 4:15
hold on a sec... if your incoming data is null, then destination column will automatically take default defined for the destination column. you just need to have check constraint ON on destination component.
Its is not, I get the following error:
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
[OLE DB Destination [16]] Error: There was an error with input column "MaritalStatus" (107) on input "OLE DB Destination Input" (29). The column status returned was: "The value violated the integrity constraints for the column.".
Regards,
Syed Mehroz Alam
My Blog | My Articles
"Error in integrity constraints".... Could you just provide the structure of your destination table.
Because i simulated this and i got it through..but yeah ..i had no constraints defined....so i just want to simulate with constraints.
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ -
19 มิถุนายน 2552 9:57
The integrity constraint is "Not Null", here's the column definition:
[MaritalStatus] [nvarchar](256) NOT NULL CONSTRAINT [DF_Dim_Patient_MaritalStatus] DEFAULT (N'N/A'),
Syed Mehroz Alam
My Blog | My Articles -
19 มิถุนายน 2552 10:27
You already have default constrain defined..... any incoming NULL value for the column will be converted to your default value.
You can remove NOT NULL atleast for ETL cycle.You will always have default value where ever source column is NULL
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/- ทำเครื่องหมายเป็นคำตอบโดย Syed Mehroz Alam 19 มิถุนายน 2552 10:59
-
19 มิถุนายน 2552 10:59
I removed the NOT NULL constraint and it worked successfully.
However, the resulting table structure is not much robust since now we can manually insert a "NULL" into that column. e.g. Run the following code as an example:
Declare @Test table ( id int identity not null, column1 nvarchar(30) null default ('n/a'), column2 nvarchar(30) null default ('n/a') ) insert into @Test(column1) values (null) select * from @Test
However, atleast I have the answer to my original question: How to insert column defaults while the incoming stream is null. Thanks for your time.
Regards,
Syed Mehroz Alam
My Blog | My Articles -
31 มกราคม 2554 15:57
You already have default constrain defined..... any incoming NULL value for the column will be converted to your default value.
You can remove NOT NULL atleast for ETL cycle.You will always have default value where ever source column is NULL
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
Wondering if you could help out here, I'm sure it's something simple that I am missing but I do not see this behaviour in my SQL 2008 instance.I have a simple Source->Destination dataflow. One of the destination columns is a smalldatetime field, not-nullable, with a default set. When a NULL comes through from the source for that field it error's out. Am I just missing a setting?
Thanks
R -
13 มิถุนายน 2555 14:54
You already have default constrain defined..... any incoming NULL value for the column will be converted to your default value.
You can remove NOT NULL atleast for ETL cycle.You will always have default value where ever source column is NULL
Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/That is not necessarily true. The default contraint is only applied when the column is ommited in the INSERT statement. That is the issue with SSIS package. It is trying to insert a NULL value into a column that does not allow NULLs. Two ways to fix this. Allow NULLs or use a derived column transformation to to apply a default value to all NULLs by using ISNULL([Column_Name]) ? "DEFAULT" : [Column_Name]. Make sure to REPLACE the column in the Derived Transformation.
Hope this helps.
Paul
.png)
