Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

avoid special symobles in ssis Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 11:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 204, Visits: 4,481
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.
Post #1569139
Posted Friday, May 9, 2014 12:58 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 624, Visits: 1,129
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.
Post #1569148
Posted Friday, May 9, 2014 2:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 204, Visits: 4,481
without chang datatype we need to do this one.
Post #1569174
Posted Friday, May 9, 2014 4:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 624, Visits: 1,129
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.
Post #1569196
Posted Friday, May 9, 2014 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,045, Visits: 6,784
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.

Post #1569208
Posted Wednesday, May 14, 2014 1:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 204, Visits: 4,481
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.
Post #1570656
Posted Wednesday, May 14, 2014 1:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 624, Visits: 1,129
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.
Post #1570668
Posted Wednesday, May 14, 2014 2:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 204, Visits: 4,481
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]+$")) ".
Post #1570673
Posted Wednesday, May 14, 2014 4:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 624, Visits: 1,129
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


Post #1570720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse