Hi Friends, I am trying to convert an old DTS package to SSIS package. Now the issue is.. DTS package pulls data from a flat file and loads it up into an SQL Table.. On transformation tab when I click on a perticular mapping, it has a active x script which evaluates the incoming data and returns a value.. I do not know how to do this in SSIS. Here is the code from the mapping. Any help is really appriciated. Thanks, Gopu Function Main() If Trim(DTSSource("Col004")) = "" Then DTSDestination("labt_rsltd_d") = NULL Else If Not IsDate(DTSSource("Col004")) Then DTSDestination("labt_rsltd_d") = Now() Else DTSDestination("labt_rsltd_d") = DTSSource("Col004") End If End If Main = DTSTransformStat_OK End FunctionGopal

I did something very similar and I used a Script Transformation. While I do not have my code I was able to find several articles to help you: http://www.bimonkey.com/2009/09/the-script-transformation-part-2-as-a-source/ or http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/ or http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htmArthur My Blog

Thanks for the prompt reply ArthurZ..!! It is a nice way to treat script component for data transformation.. Well if I choose flat file source-->Script Component--> Oledb destination. I can make this thing work.. But I was not able to figur out.. how to read values that are passed on from the Flat file source to Script component (in script editor).. You got any idea? And I am using C# as the script language.. Thanks GopuGopal

Another alternative is to use the Derived Column Transformation in which you can modify the field content based on some logic which is done using an SSIS expression as described here: http://www.bimonkey.com/2009/08/the-derived-column-transformation/Arthur My Blog

Yupp I tried using it, but in the code if you observe there is IsDate() function which cannot be implemented using derived column. Function Main() If Trim(DTSSource("Col007")) = "" Then DTSDestination("labt_rslt_cml_d") = NULL Else If Not IsDate(DTSSource("Col007")) Then <<<--------------------------------------------------- DTSDestination("labt_rslt_cml_d") = Now() Else DTSDestination("labt_rslt_cml_d") = DTSSource("Col007") End If End If Main = DTSTransformStat_OK End Function Thanks GopuGopal