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

How to map transformation using script task in SSIS? Expand / Collapse
Author
Message
Posted Tuesday, December 04, 2007 10:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:05 AM
Points: 116, Visits: 241
Hi,
I have a DTS package which is copying records from MS access database to SQL server database..they have mapped source and destination columns using ActiveX script which also has loop inside. I need to do same thing in SSIS...any body help me out please !!

Below give is the sample code of that ActiveX Script...please help me
====================
function Main()
{
// If true, row fails. Goes to false for each row
var bFailure = false;
// Contains value of condition string
var strValue ="";

// Only runs this routine once, and sets the conditions in the global variables for the remaining rows
if (DTSGlobalVariables("GotConditions") == false)
{
// Loop through all columns
for(var i=1;i< DTSSource.count;i++)
{
//Pass in field name to lookup for return of any conditions and store it in global var
strValue = DTSLookups("getConditions").Execute(DTSSource(i).Name);
DTSGlobalVariables(DTSSource(i).Name) = strValue;
}
// So this only happens once per import process
DTSGlobalVariables("GotConditions") = true;
}
// Loop through all columns
for(var i=1;i< DTSSource.count;i++)
{
// Global var is 0 if there are no conditions
if(!(DTSGlobalVariables(DTSSource(i).name)==0))
{
// Grab Condition(s) and eval them for true
// All conditions are OR'd with each other
var strCond = ""+DTSGlobalVariables(DTSSource(i).name)+"";
if(eval(strCond))
{
bFailure = true;
}
}
}
if(isNaN(DTSSource("MonFriOpenTime24"))){bFailure=true;}
if(isNaN(DTSSource("MonFriCloseTime24"))){bFailure=true;}
if(isNaN(DTSSource("SatOpenTime24"))){bFailure=true;}
if(isNaN(DTSSource("SatCloseTime24"))){bFailure=true;}
if(isNaN(DTSSource("SUN_O"))){bFailure=true;}
if(isNaN(DTSSource("SUN_C"))){bFailure=true;}

if (bFailure)
{
// Set the Changed field to 8, a failure or duplicate row
// so the import process knows not to transform it.
DTSDestination("Changed") = 8;
DTSDestination("InvalidDesc") = "Invalid Data" ;
}

DTSDestination("Post Date") = DTSSource("Post Date");
DTSDestination("Entity_ID_NK") = DTSSource("Entity ID");
DTSDestination("Division_ID_NK") = DTSSource("Division ID");
DTSDestination("DC_ID_NK") = DTSSource("DC ID");
DTSDestination("Store_No_nk") = DTSSource("Store no");
DTSDestination("DCID") = DTSSource("DCID");
DTSDestination("Division_Name") = DTSSource("Division Name");
DTSDestination("DC_Name") = DTSSource("DC Name");
DTSDestination("Store_Name") = DTSSource("STORE NAME");
DTSDestination("Business_Name") = DTSSource("Business Name");
DTSDestination("NAPA_Name") = DTSSource("NAPA NAME");
DTSDestination("Branch_Name") = DTSSource("Branch Name");
DTSDestination("Store_Type_Code") = DTSSource("Store Type Code");
DTSDestination("Store_Type_Name") = DTSSource("Store Type Name");
}
===============
Post #429623
Posted Wednesday, December 05, 2007 12:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:50 AM
Points: 451, Visits: 513
May be to use conditional split component from source and on this basis add destinations for each condition?
Post #429653
Posted Wednesday, December 05, 2007 2:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:05 AM
Points: 116, Visits: 241
But how do i use for loop in here as for loops are not allowed in data flow task?
Post #429685
Posted Wednesday, December 05, 2007 2:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:50 AM
Points: 451, Visits: 513
Please explain what you want to your package do. Sorry but it is difficult to analyze code ;).
Post #429696
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse