ActiveX script to SSIS
-
Function Main()
Dim objFSO 'File System Object
Dim outputfile 'Output File Name
Dim adoconnection 'ADO Connection Handle
Dim rs 'ADO Recordset object
Dim sSql 'A String variable to store SQL statements
Dim sGroupDetailRecord 'Variable to store group detail header
Dim sCustomerHeader 'Variable to store customer header record
Dim lnFieldIndex 'Integer field to iterate recordset columns
Dim sClientId 'Variable to store ClientID
Dim sDateTimeString 'A variable to store current date in YYYYMMDD format
Dim lnClientCount'Variable to hold the count of each client Id
Dim sOutputFileName
Dim lnHeaderCount
sDateTimeString = datepart("YYYY",Date) & Right("00" & DatePart("m",Date),2) & Right("00" & DatePart("d",Date),2)
'Inititalize and open the output file
Set objFSO = CreateObject("Scripting.FileSystemObject")
sOutputFileName = DTSGlobalVariables("OutputFileName").Value
'delete the file if it is already found
if objFSO.FileExists(sOutputFileName) then
objFSO.DeleteFile sOutputFileName
end if
Set outputfile = objFSO.OpenTextFile(sOutputFileName, 2,True)
'Build the Header Record
tmpstr = "0001420000G" + sDateTimeString + "000000" + sDateTimeString + sDateTimeString + Space(209)
sSql = "exec dbo.pr_Extract_Args_Grps401_Read"
'Establish an ADO connection
Set adoconnection = CreateObject("ADODB.Connection")
adoconnection.Open DTSGlobalVariables("ConnectionString")
adoConnection.CommandTimeout = 300
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.CursorType = 0
Setrs.ActiveConnection = adoconnection
rs.open sSql
' Set rs = adoconnection.Execute(sSql, , 0) ' opened a forward only cursor
Set rs.ActiveConnection = Nothing
If Not rs.EOF Then
outputfile.writeline tmpstr 'write the header record
sClientId = ""
lnClientCount = 0
Do While Not rs.EOF
sGroupDetailRecord = ""
' Build one Customer Header Record for every client ID
If sClientId <> rs(2) Then
'First write the previous client trailer record
If lnClientCount <> 0 Then
outputfile.writeline "900142" + sClientId + Right("000000" + CStr(lnClientCount),7)+space(233)
lnClientCount = 0
End if
sClientId = rs(2)
'Customer Header Record
sCustomerHeader = "010142" + sClientId + "F" + sDateTimeString + "000000" + sDateTimeString + sDateTimeString + Space(209)
outputfile.writeline sCustomerHeader
lnHeaderCount = lnHeaderCount+1
End If
For lnFieldIndex = 0 To rs.Fields.Count - 1
sGroupDetailRecord = sGroupDetailRecord + rs(lnFieldIndex)
Next
outputfile.writeline sGroupDetailRecord
lnClientCount = lnClientCount + 1
rs.MoveNext
Loop
'write the last client Trailer Record
outputfile.writeline "900142" + sClientId + Right("000000" + CStr(lnClientCount),7)+space(233)
'write the file trailer record
outputfile.writeline "9901420000" + Right("000000" + CStr(lnHeaderCount),7)+space(233)
outputfile.Close 'close the output file
else
'No Data returned by the SP.
'Write a blan file with only the file header and file trailer information
outputfile.writeline tmpstr 'write the header record
outputfile.writeline "9901420000" + Right("000000" + CStr(lnHeaderCount),7)+space(233)
outputfile.Close 'close the output file
End If
rs.Close
Set rs = Nothing
adoconnection.Close
Set adoconnection = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Why did you post this?
-
Can any one please tell me how to convert this Activex script into SSIS tasks.
-
I don't want to be rude, but you cannot expect people from the forum to do the translation job for you. You have to either learn how to convert the script yourself or pay someone to do it.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply