October 26, 2007 at 5:46 am
Hi,
I have activex script in my DTS package but i want to convert this into SSIS, I need to know which is the best solution in Script task,activex script task or using variables and control loops??? below given is my activex code please any body help me in finding solution
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
FUNCTION Main()
Dim oPkg, oTaskProperties
Dim iTaskCount, iPropertyCount, iMaxPackageVersion
Dim strTaskFileName
' SET OBJECT TO CURRENT PACKAGE
SET oPkg = DTSGlobalVariables.Parent
' LOOP THROUGH ALL PACKAGE TASKS TO DETERMINE WHICH ARE "Execute Package Task" OBJECTS
FOR iTaskCount = 1 to oPkg.Tasks.Count
' IF PACKAGE IS "Execute Package Task" SET GUID
IF isExecutePackageTask(oPkg.Tasks(iTaskCount).Properties) THEN
' GET THE FILE PATH AND NAME FROM THE TASK
strTaskFileName = oPkg.Tasks(iTaskCount).Properties.Item("FileName")
' GET PACKAGE GUID FROM THE FILE LOCATION
SET oInfoCollection = oPkg.GetSavedPackageInfos(strTaskFileName)
' SET THE MAX VERSION ID TO GET THE GUID
iMaxPackageVersion = oInfoCollection.Count
' SET THE GUID
oPkg.Tasks(iTaskCount).Properties.Item("PackageID").Value = oInfoCollection.Item(iMaxPackageVersion).PackageID
END IF
NEXT
' CLEAN UP
SET oPkg = NOTHING
SET oInfoCollection = NOTHING
Main = DTSTaskExecResult_Success
END FUNCTION
'**********************************************************************
' FUNCTION:
'isExecutePackageTask
' ARGUMENTS:
'oTaskProperties - Local Task Properies
' RETURNS:
'(boolean)
' PURPOSE:
'Determine if a task is an "Execute Package" Task
'************************************************************************
FUNCTION isExecutePackageTask(oTaskProperties)
' LOCAL VARIABLES DECLARATION
DIM iPropertyLoop, RTN
' INITIALIZE RETURN VARIABLE
RTN = FALSE
' LOOP THROUGH TASK'S PROPERTIES. IF "PackageID" EXISTS IN THE
' PROPERTIES, THEN THE TASK IS AN "Execute Package" TASK
FOR iPropertyLoop = 1 TO oTaskProperties.Count
IF oTaskProperties(iPropertyLoop).Name = "PackageID" THEN
RTN = TRUE
EXIT FOR
END IF
NEXT
' RETURN
isExecutePackageTask = RTN
END FUNCTION
October 26, 2007 at 6:02 am
I'm not sure whether this give us atleast 25% satisfication to which result set you have been aiming for.. but thought of send this link....
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21803338.html
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply