Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


avoid special symobles in ssis


avoid special symobles in ssis

Author
Message
asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
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.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 2597
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.
asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
without chang datatype we need to do this one.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 2597
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.
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7968 Visits: 9425
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.


asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
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.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 2597
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.
asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
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]+$")) ".
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 2597
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search