i learning use ssis 2012 script component (c#) transform values based on list of possible conditions.
i have used == operator compare input row exact string values in switch block, when tried .contains() method in if-else block, rows returned case else.
i show both code attempts below. using .contains() method , double-checked msdn. looks me if using method correctly.
is there reason why can exact == operator evaluation not .contains() evaluation?
public override void input0_processinputrow(input0buffer row) { // block **not** succeed in evaluating row.mealcode value // .contains("string") method. rows evaluate case else. if (row.mealcode.contains("free")) { row.tmealcode = "free"; } else { row.tmealcode = "else"; } // code evaluates value of mealcode // in each row , correctly outputs tmealcode value. switch (row.mealcode) { case "free lunch": case "free": row.tmealcode = "free"; break; case "reduced lunch": case "red": row.tmealcode = "reduced"; break; case "reg": row.tmealcode = "regular"; break; default: row.tmealcode = "else"; break; } } to provide more context, screenshot shows have placed script component before ole db destination task.
from referenced msdn
returns value indicating whether specified substring occurs within string.
assume source data contains following values
- free
- free
- free
- free
- free
.contains("free") going match first value because going case sensitive comparison.
you've done same in switch statement - you've coded various casings you've encountered.
you need either case insensitive comparison how can case insensitive string comparison?
or if you're set on using contains method, ensure both arguments cased appropriately.
if (row.mealcode.toupper().contains("free".toupper())) 
biml
biml, business intelligence markup language, platform business intelligence. here, we're going use describe etl. bids helper, free add on visual studio/bids/ssdt improves development experience. specifically, we're going use ability transform biml file describing etl ssis package. has added benefit of providing mechanism being able generate solution i'm describing versus clicking through many tedious dialogue boxes.
after installing it, add new biml file ssis project , edit line 5 point valid sql server instance. right click on biml file , select generate ssis package.
here's example of biml required generate ssis package intended logic using contains
<biml xmlns="http://schemas.varigence.com/biml.xsd"> <connections> <connection name="tempdb" connectionstring="data source=.\dev2008;initial catalog=tempdb;provider=sqlncli11.1;integrated security=sspi;" /> </connections> <packages> <package name="so_31330881"> <tasks> <dataflow name="dft sample"> <transformations> <oledbsource connectionname="tempdb" name="olesrc dbo_source"> <directinput>select d.mealcode (values ('free'), ('free'), ('free')) d(mealcode);</directinput> </oledbsource> <scriptcomponenttransformation projectcorename="sc_31330881" name="scr transform values"> <scriptcomponentprojectreference scriptcomponentprojectname="sc_31330881" /> </scriptcomponenttransformation> <derivedcolumns name="der placeholder" /> </transformations> </dataflow> </tasks> </package> </packages> <scriptprojects> <scriptcomponentproject projectcorename="sc_31330881" name="sc_31330881"> <files> <file path="main.cs"> using system; using system.data; using system.web.services; using system.text; using system.xml; using microsoft.sqlserver.dts.pipeline.wrapper; using microsoft.sqlserver.dts.runtime.wrapper; [microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute] public class scriptmain : usercomponent { public override void input0_processinputrow(input0buffer row) { // if (row.mealcode.contains("free")) if (row.mealcode.toupper().contains("free".toupper())) { row.tmealcode = "free"; } else { row.tmealcode = "else"; } } } </file> <file path="properties\assemblyinfo.cs"> using system.reflection; using system.runtime.compilerservices; // // general information assembly controlled through following // set of attributes. change these attribute values modify information // associated assembly. // [assembly: assemblytitle("sc_31330881")] [assembly: assemblydescription("")] [assembly: assemblyconfiguration("")] [assembly: assemblycompany("")] [assembly: assemblyproduct("sc_31330881")] [assembly: assemblycopyright("copyright @ 2014")] [assembly: assemblytrademark("")] [assembly: assemblyculture("")] // // version information assembly consists of following 4 values: // // major version // minor version // build number // revision // // can specify values or can default revision , build numbers // using '*' shown below: [assembly: assemblyversion("1.0.*")] </file> </files> <assemblyreferences> <assemblyreference assemblypath="system" /> <assemblyreference assemblypath="system.data" /> <assemblyreference assemblypath="system.web.services" /> <assemblyreference assemblypath="system.windows.forms" /> <assemblyreference assemblypath="system.xml" /> <assemblyreference assemblypath="microsoft.sqlserver.txscript.dll" /> <assemblyreference assemblypath="microsoft.sqlserver.dtsruntimewrap.dll" /> <assemblyreference assemblypath="microsoft.sqlserver.dtspipelinewrap.dll" /> <assemblyreference assemblypath="microsoft.sqlserver.pipelinehost.dll" /> </assemblyreferences> <inputbuffer name="input 0"> <columns> <column codepage="1252" datatype="ansistring" length="10" name="mealcode" usagetype="readonly" /> </columns> </inputbuffer> <outputbuffers> <outputbuffer issynchronous="true" name="output 0"> <columns> <column codepage="1252" datatype="ansistring" length="10" name="tmealcode" /> </columns> </outputbuffer> </outputbuffers> </scriptcomponentproject> </scriptprojects> </biml> 
Comments
Post a Comment