Help Converting Activex to Script task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Help Converting Activex to Script task

Author

Topic

karanjv
Starting Member

3 Posts

Posted - 2008-08-22 : 07:54:57

Hi,
I am trying to migrate from DTS to SSIS. We tried using the migrate tool but no luck mainly in actvex part. Am giving the active x script below wat we are using

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

DTSGlobalVariables("G_PROCESSABLE").Value = 0
If FolderContainsAllFiles (DTSGlobalVariables("G_NONPROCESSED_FOLDERPATH").Value) = True Then
DTSGlobalVariables("G_PROCESSABLE").Value =1

End If
Main = DTSTaskExecResult_Success

End Function

'This function checks if all the files are available for processing
'If all are avilable, sets appropriate file names and inserts audit records

Function FolderContainsAllFiles (strFolderPath)

Dim oFileObject,oFolder,oFolderFiles

SET oFileObject = CreateObject("Scripting.FileSystemObject")
SET oFolder = oFileObject.GetFolder(strFolderPath)
SET oFolderFiles = oFolder.Files

'Initialise variable that will hold the file name without folder path
strFile=""

' Set values to NULL/FALSE initially
FolderContainsAllFiles = False
FileCount = 0
FOR EACH oFile IN oFolderFiles
strFile = oFile.Name
'Check if AEF file is available. If yes,set the datestamp.

if UCase(Left( strFile , LEN(DTSGlobalVariables("G_AEFNETASSET_FILENAME").Value))) = UCase(DTSGlobalVariables("G_AEFNETASSET_FILENAME").Value) then
DTSGlobalVariables("G_DATESTAMP").Value = right(strFile,12)

FOR EACH oInnerFile IN oFolderFiles
fileDate = DTSGlobalVariables("G_DATESTAMP").Value
if(checkdate(fileDate)) then
strInnerFile = oInnerFile.Name
DTSGlobalVariables("G_AUDITFILENAME").Value = oInnerFile.Name

'Set AEF_NETASSET file name
if UCase(strInnerFile) = UCase(DTSGlobalVariables("G_AEFNETASSET_FILENAME").Value&DTSGlobalVariables("G_DATESTAMP").Value) then
FileCount = FileCount+1
DTSGlobalVariables("G_AEFNETASSET_FILENAME").Value = oInnerFile
DTSGlobalVariables("G_AEFNET_FEEDAUDITID").Value= InsertAuditRecord()
End If
'Set WEF file name
if UCase(strInnerFile) = UCase(DTSGlobalVariables("G_WEFNETASSET_FILENAME").Value&DTSGlobalVariables("G_DATESTAMP").Value) then
FileCount = FileCount+1
DTSGlobalVariables("G_WEFNETASSET_FILENAME").Value = oInnerFile
DTSGlobalVariables("G_WEFNET_FEEDAUDITID").Value = InsertAuditRecord()
End If
End If

NEXT
Exit For

End If

NEXT

set oFileObject = Nothing
set oFolder = Nothing
set oFolderFiles = Nothing

if FileCount = 2 then
FolderContainsAllFiles = True
else
FolderContainsAllFiles = False
end if

End Function

Function InsertAuditRecord()
Dim objDBCon, objRS, strJobTime
set objDBCon = CreateObject("ADODB.Connection")
set objRS = CreateObject("ADODB.Recordset")

strCon = DTSGlobalVariables("G_DBCONNECTIONSTRING").Value
objDBCon.Open strCon

strFile = DTSGlobalVariables("G_AUDITFILENAME").Value

strQuery ="INSERT INTO tblFeedAudit (FeedFileName,LoadDate) SELECT '"+strFile+"',GETDATE() "
objDBCon.Execute( strQuery)

strQuery ="select max(FeedAuditId) as FeedAuditId from tblFeedAudit"
Set objRS = objDBCon.Execute( strQuery)
InsertAuditRecord = objRS.Fields(0)
'DTSGlobalVariables("G_FEEDAUDITID").Value= objRS("FeedAuditId")

objRS.Close
objDBCon.Close

Set objDBCon = Nothing
Set objRS = Nothing
Set strJobTime = Nothing

End Function

Function checkdate(fileDate)
checkdate = false

Dim objDBCon, objRS
set objDBCon = CreateObject("ADODB.Connection")
set objRS = CreateObject("ADODB.Recordset")

strCon =DTSGlobalVariables("G_DBCONNECTIONSTRING").Value
objDBCon.Open strCon

if (len(fileDate) = 12 ) then
strDate = Mid (fileDate,1,2)&"/"&Mid(fileDate,3,2)&"/"&Mid(fileDate,5,4)

strValiddate = "Select ISDATE('"&strDate&"') AS dateChk"
set objRecordset = objDBCon.Execute(strValiddate)

IF NOT objRecordset.EOF Then
Strchkdate = objRecordset("dateChk")
END IF

If ( Strchkdate = 1) Then
checkdate = true
Else
checkdate = false
End If
End If
End Function

The variable I can acces via Dts.Variables in SSIS but how do i acces the databse and get recordsets. I got suggestion like using using recordset and looping but i dont think i can implement the same logic as in script without using script task feature of SSIS.

Please do help. Awaiting ur response.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-22 : 09:15:14

Just headsup for you:

http://pragmaticworks.com/community/blogs/dtsxchange/archive/2007/09/08/Converting-the-ActiveX-Script-Task.aspx