C# script in SSIS Script Component to insert data

Posted by Sriharim under Sql Server on 5/6/2015 | Points: 10 | Views : 2662 | Status : [Member] | Replies : 2
I want to identify null data in eid,firstname,did columns. Also, to find the which column has null value and to save the below converted integers(s and p variable) into new columns.

In below code, columns are: eid, did, firstname,lastname, ss,pp, problem, Ifgood.

Here problem is, i am unable to copy the s and p variable data into ss and pp columns.
That is : s = Convert.ToInt32(Row.eid);
Row.ss = s;
After executing below code, the data in ss and pp columns is 0.
eid,did,firstname,lastname has data in the columns.

Entire code is:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.eid_IsNull || Row.FirstName_IsNull || Row.DID_IsNull)
{
Row.Problem = "At least one column is not filled in";
Row.Ifgood = false;
return;
}
else
{
Row.Ifgood = true;
return;
}
// try to convert eid/did numbers to integers

int s = 0;
int p = 0;
try
{
s = Convert.ToInt32(Row.eid);
Row.ss = Convert.ToInt32(Row.eid);
}
catch
{
s = 0;
}
try
{
p = Convert.ToInt32(Row.DID);
Row.pp = Convert.ToInt32(Row.DID);
}
catch
{
p = 0;
}
// if either still 0, we couldn't convert
if (s == 0 || p == 0)
{
Row.Problem = "The Eid and Did numbers aren't both integers";
Row.Ifgood = false;
return;


}

}


please help me, how to copy converted data of eid and did into ss and pp columns in script component.

Mark as Answer if its helpful to you
---
Srihari



Responses

Posted by: Bandi on: 5/6/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
can you explian what is the problem here in words, not thorugh code....

Are you wanted to check for nullability of columns?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/6/2015 [Member] Starter | Points: 25

Up
0
Down
Yes, to check for nullability of columns.
Below Tables has eid,firstname,lastname,middlename,did columns
1	Guy	Gilbert	R	guy1@adventure-works.com	                    9
2 Kevin Brown F kevin0@adventure-works.com 8
3 Roberto Tamburello NULL roberto0@adventure-works.com 2
4 Rob Walters NULL rob0@adventure-works.com NULL

i will dump the data into good data and bad data tables based on nulls,
In earlier post, code checks all the rows of columns, if a value is null then in problem column, will have details.
Like, bad data table below (columns are eid,firstname,lastname,middlename,did,problem,ifgood)

4	Rob	Walters	NULL	rob0@adventure-works.com	NULL	Atleast one column is not filled in	0


Now, In problem column,i need have details of which column is null (among eid,firstname and did columns) and to copy the data of eid & did into new columns after converting into integer in script component


Thanks in Advance

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response