May 8, 2014 at 11:56 pm
I have small doubt in ssis. please tell me how to solve this issue
source table :emp id ,name having datatype varchar(30),sal money
id| name | sal
1 | abc |100
,2"| def |2500
3 | ",rac"|1400
4 | ven |200
"5",| jai |100
10| ha |50
when we load this data into target table that time we need to load correct data
target table datatypes also id,name having dataype varchar(30),sal money
target table data want look like below
target table : emptarg
id | name | sal
1 | abc |100
4 | ven |200
10 | ha | 50
and errore records load into separate table
error table:emperror
id | name | sal
,2" | def |2500
3 | ",rac"|1400
"5",| jai |100
when source data having special symoble that records need to send error table .how to write condition in conditional split or any other ways.
May 9, 2014 at 12:58 am
use 'Data Conversion' in you Data Flow, try to convert the ID column in Int,
click Configure Error Output, on Error Column Change the Fail Component to Redirect Rows.
Create a another table for that and drag the RED flow to that table.
May 9, 2014 at 2:30 am
without chang datatype we need to do this one.
May 9, 2014 at 4:13 am
asranantha (5/9/2014)
without chang datatype we need to do this one.
is there some kind of restriction ?
if its a yes then you can place a Script Task, create a Boolean flag to check if the id column is a integer or not, then use a conditional split on it.
May 9, 2014 at 5:08 am
Or just a Conditional Split with several nested REPLACE to replace 0-9 with empty string and test the result for empty string
Far away is close at hand in the images of elsewhere.
Anon.
May 14, 2014 at 1:00 am
1)In Source side having Only one column i choosen for test purpose and that field name is name;
2)after that i used script component and select name field and oupput columns
3)i added isvalid and derivedname isvalid datatype is boolean and derivedname is varchar(20)(i given varchar /boolean two cases i test). then used script component in below code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Text.RegularExpressions;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
bool isvalid = true;
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();`enter code here`
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{if (Regex.IsMatch(Row.name, "^,:@[a-zA-Z]+$"))
{
Row.Derivedname = Row.name;
}
else
{
isvalid = false;
}
Row.isvalid = isvalid;
}
5)then used conditional split applied condition
isvalid=false 6)then destination table configured.but still not gettting exactely result.please tell me where i done mistick.
May 14, 2014 at 1:40 am
As far as i remember, you actual issue was on ID column not the Name Column
update your code like this:
{if Char.IsNumber(Row.ID)
{
Row.isvalid= true;
}
else
{
Row.isvalid= false;
}
}
1) you donot need an extra bool variable in it
2) Derivedname is not required so it been removed.
3) apply the conditional split on isvalid field only.
after applying this change, run the package and the share the results.
May 14, 2014 at 2:00 am
when i used
{if Char.IsNumber(Row.ID)
{
Row.isvalid= true;
}
else
{
Row.isvalid= false;
}
}
This code its showing error for this line "if Char.IsNumber(Row.ID)"
here we didnot give any checking special character/symbols and its finaly show error
the best overload method matachs for char.isnumber(char')as some invalid arguments.
cannot we give like this condition
" if (Regex.IsMatch(Row.id, "^,:@[a-zA-Z]+$")) ".
May 14, 2014 at 4:39 am
Sorry, i just noticed i have replaced your code with mine. This function is also valid, but for single Character only.
Following is the complete code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Text.RegularExpressions;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();`enter code here`
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Regex.IsMatch(Row.ID, "^(0|[1-9][0-9]*)$"))
{
Row.isvalid = true;
}
else
{
Row.isvalid = false;
}
}
I have updated the regular expression. hope it helps
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply