Hello everyone ... i have to write package(s) for a data transformation between two databases with different schemas. in this proccess i have to use sql functions to read data from first and after process value then transfer this to dest. table.. i have to convert source data for example: string ret ="ADE": switch(ret) { case "aaa": return 1; case "qqq": return 1001; case " ADE": return 2001; default : return -1; } the %60 of the data that i have to convert have to be proccesed like this... because the source envoirement is very old designed .so here my questions: 1-) can anybody give me an example/or a place that similar to my situation..or a starting point.. 2-)in c# we write a static method and use it anywhere we want i,n the project with publc keyword .in SSIS has something similar to it? because i searched a lot and did not find /read something like that.. anyway thanks for replies... evgeni

If you want some reusability, you might look at the Biml functionality in BIDS Helper (open source add-in for BIDS). Biml includes the ability to define common logic and reuse it across multiple packages. John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

It's fairly easy with SSIS. You need to use a Data Flow Task. Inside that, you'd use a Source component - probably an OLE DB Source - to connect to your source table and retrieve a rowset. You'd then use a Derived Column component to generate some new columns using an analogue to the switch statement you've shown above. Then that rowset would be sent to your destination table using a Destination component - probably an OLE DB Destination. If you're just starting out with SSIS, you probably want to review the FAQ in this forum for links to training. Talk to me now on

Can you explain what kind of "static method" you're hoping to use? Give us an example? Talk to me now on

do all steps as Todd explained, just for derived column you can use this expression to implement the case statement you want: [ret]=="aaa" ? 1 : ( [ret]=="qqq" ? 1001 : ( [ret]=="ADE" ? 2001 : -1 ) )http://www.rad.pasfu.com

Thanks Todd and Reza... your answers cleared my mind .but for my second question i can't get an answer.. 2-)in c# we write a static method and use it anywhere we want i,n the project with publc keyword .in SSIS has something similar to it? because i searched a lot and did not find /read something like that.. and aplus for these questions can i use (or if possible how) user defined functions in derived columns, or is there some other way to use user defined functions? .. evgeni

in SSIS you can create CUSTOM TASK or component, and use it everywhere you want. this is how you can create custom tasks and components: http://msdn.microsoft.com/en-us/library/ms135965.aspx http://msdn.microsoft.com/en-us/library/ms136078.aspxhttp://www.rad.pasfu.com