I have a simple DTS package I need to rewrite as an SSIS package: Flat File -> Data Transform Task -> SQL Server Table Within the Data Transform Task is an ActiveX Script: '********************************************************************** ' Visual Basic Transformation Script '************************************************************************ Function Main() DTSDestination("budget_site") = DTSSource("Col001") DTSDestination("tag_site_id") = FixSite(DTSSource("Col002")) DTSDestination("budget_year") = DTSSource("Col003") DTSDestination("check_date") = ScrubDate(DTSSource("Col004")) Main = DTSTransformStat_OK End Function Function ScrubDate (strDateParm) 'Function inverts YYYMMDD date to MM/DD/YY format 'Evaluate Variable If IsNull(strDateParm) Or strDateParm = "000000{" Or Len(Trim(strDateParm)) < 3 Then ScrubDate = Null Exit Function End If 'Insert slashes strDate = Mid(strDateW, 4, 2) & "/" & Right(strDateW, 2) & "/" If Left(Trim(strDateW), 1) = "1" Then strDate = strDate & "20" & Mid(strDateW, 2, 2) Else If Left(Trim(strDateW), 1) = "0" Then strDate = strDate & "19" & Mid(strDateW, 2, 2) Else ScrubDate = Null Exit Function End If End If If IsDate(strDate) Then ScrubDate = strDate Else ScrubDate = Null End If End Function Function FixSite(strSiteParm) 'Evaluate Variable If IsNull(strSiteParm) Or Len(Trim(strSiteParm)) < 3 Then FixSite = Null Exit Function End If FixSite = "0" & strSiteParm End Function Here is my question: I DO NOT want to preserve this ActiveX Script when this package is moved to SQL Server 2005. So within SSIS, what is it that I can use to replace the Data Transform Task where I can manipulate and control each column in each record?

you can use SCRIPT COMPONENT TRANSFORMATION to write a script which will apply on EACH record of data stream. and you can write your script with few changes there. but note that there are lots of transformations in data flow task which will help you in many things, tell us what you want to do in the script and we tell you if you could use other transforms instead of scripting or not.http://www.rad.pasfu.com

Thanks Reza for your reply. I process a lot of mainframe files that contain raw, unedited data. For example, I get a lot of ill formed date fields that need to be manipulated. The ScrubDate function in my original post is an example of having to do that. I'm going to research SCRIPT COMPONENT TRANSFORMATION and hopefully I can use that. I also receive a lot of packed signed numeric data that is mainframe-speak for weird numeric data. I've noticed in SSIS that there is a standard transformation for that type of data so I won't have to do anything special for that.

yes, if you need to implement your own validation procedure , SCRIPT COMPONENT can be good position.http://www.rad.pasfu.com

It's not always necessary to bring out the "big gun" of the Script component. The standard "data cleansing" component of SSIS is the Derived Column component. SSIS has it's own expression language that you can use to restructure contents of columns and/or create new columns with more desireable contents on a row-by-row basis. The Derived Column does have it's advantages (it's fast) - but it does have issues as well (it's own expression language that you'll have to learn, which does have limits). Talk to me now on