ssis querry
Hi, i am reading file through ssis , follwing output i get column 0 column 1 column 3 1 2 3 null null null null null null null null null null null null null null null i want all the null values for the column replace by first row value of column means, i want following output column 0 column 1 column 3 1 2 3 1 2 3 1 2 3l 1 2 3 1 2 3 how can do this through ssis, plz reply
May 20th, 2012 6:45am

One method is to insert the data into SQL Server and then handle it create table #t (c1 int,c2 int) insert into #t values (1,2) insert into #t values (null,null) insert into #t values (null,null) insert into #t values (null,null) insert into #t values (null,null) update #t set c1=(select top 1 c1 from #t where c1 is not null), c2=(select top 1 c2 from #t where c2 is not null) Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2012 7:09am

hi, can this achieved though derived column, plz let me know what condition i used in derived column
May 20th, 2012 7:27am

you can solve this problem using two methods. T sql in ssis and pure ssis 1)tsql declare @firstcolumn int declare @secondcolumn int declare @thirdcolumn int SELECT TOP(1) @firstcolumn=column0,@secondcolumn=column1,@thirdcolumn=column2 FROM myTable where column0 is not null and column2 is not null and column3 is not null SELECT isnull(column0,@firstcolumn)... from mtable 2)Bi in ssis create three variable for this which store three value and in derived column you can use it. Thanks. Regards, nishantcomp2512 Please mark posts as answered where appropriate
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2012 2:01pm

You should not be doing this in T-SQL if your data is sourced somewhere else. Your T-SQL solution will also not work if rows sometimes have NULLs, but sometimes don't - and the "most recent" value is to be used in subsequent rows. You also can NOT accomplish this using a Derived Column and/or variables. The T-SQL statement again assumes that only one row will have non-NULL values (which I'm assuming is not correct). The only way to do this according to the phrasing the OP gave is to use a Script component. At the class level in the script, define variables for each column that may be NULL in your data that you want to use a prior non-NULL value for. In the ProcessInputRow method, examine each column. If it's null, then replace it with the saved value. If it's not, set the variable to the row's value, like this: if (Row.column0_IsNull) { Row.column0 = this.column0 } else { this.column0 = Row.column0 } Talk to me now on
May 20th, 2012 6:39pm

Hi Todd, if (Row.column0_IsNull) { Row.column0 = this.column0 } else { this.column0 = Row.column0 } how to saved the value for the variable which is not null, becoz i am not getting (this.column0)
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2012 1:27am

Other ways could be: Approach One: Create one int type variable (CntLoop) )which will contain the number of row with all values as null. Select Count(*) From table Where Column1 IS NULL Column2 is NULL and Column3 is NULL Place one for each loop for item enumerator and run loop as many time as the value of (CntLoop+1). Place a data flow task inside loop one source which will contain DB source query: Select * From table Where Column1 IS NOT NULL and Column2 IS NOT NULL and Column3 IS NOT NULL. Approach Two: Create three variable for holding value of three columns and create a dynamic SQL statement which will be(Pseudo code): Select ISNULL(Column1, @Var1) As Column1, ISNULL(Colum2, @Var2) As Colum2, ISNULL(Column3, Var3) As Column3 FROM Table put this query in a variable expression and connect to destination. Hope this will help.
May 23rd, 2012 8:32am

Read the earlier part of my post - you need to add variables to the script class. Right after "public class ScriptMain", you'll want to add some "private string column0;" lines (with correct data types) for each column you're working with. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2012 12:04pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics