How to map transformation using script task in SSIS?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to map transformation using script task...

How to map transformation using script task in SSIS?

Rate Topic

Display Mode

Topic Options

Author

Message

guptaopus

Posted Tuesday, December 04, 2007 10:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:17 AM
Points: 116, Visits: 240

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

Powichrowski Marek

Posted Wednesday, December 05, 2007 12:56 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 12:37 PM
Points: 451, Visits: 498

May be to use conditional split component from source and on this basis add destinations for each condition?

Post #429653

guptaopus

Posted Wednesday, December 05, 2007 2:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:17 AM
Points: 116, Visits: 240

But how do i use for loop in here as for loops are not allowed in data flow task?

Post #429685

Powichrowski Marek

Posted Wednesday, December 05, 2007 2:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 12:37 PM
Points: 451, Visits: 498

Please explain what you want to your package do. Sorry but it is difficult to analyze code ;).

Post #429696

« Prev Topic | Next Topic »

Permissions