December 4, 2007 at 10:17 pm
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");
}
===============
December 5, 2007 at 12:56 am
May be to use conditional split component from source and on this basis add destinations for each condition?
December 5, 2007 at 2:25 am
But how do i use for loop in here as for loops are not allowed in data flow task?
December 5, 2007 at 2:44 am
Please explain what you want to your package do. Sorry but it is difficult to analyze code ;).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply