Hi 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

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.

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

Hi,I don't think there is anything like replacing nulls as you are saying in SSIS.you can do wht GarthH_BI siad.ThanksRohit

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/

You 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

I doubt if we can read the default constraint in SSIS from sql server Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

Of 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

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.Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

Try thisINSERT <Table Name>DEFAULT VALUESThis will insert :1. Default Values for all columns where Defaults are specified2. 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 columnINSERT <TableName>col 1col 2valuesval1,DEFAULTThis 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

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. Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ Its is not, I get the following error: [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

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. Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/ Its is not, I get the following error: [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/

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

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/

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

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