'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg
Dim oConn
Dim oMon
Dim oPump
Dim iYear
Dim sSQL
Dim iX
Dim iY
Dim iMon
Dim sSvr
Dim oTime
Dim iStartYear
Dim iCount
Dim iZ
Set oPkg = DTSGlobalVariables.Parent 'Get package object
Set oConn = CreateObject("ADODB.Connection") 'Create a connection object
Set oRec = CreateObject("ADODB.Recordset") 'Create a recordset object
sSvr = oaksql11 'Get the location of the SQL Server
oConn.Open "Driver={SQL Server};Server=" & sSvr & ";Database=MABCD_Staging" 'Open a connection to SQL Server
oRec.Open "Select 'Year'= SUBSTRING(Sales_RefreshGLPeriod,1,4), 'Month'=SUBSTRING(Sales_RefreshGLPeriod,5,2) From dbo.KWSalesRefresh Where Sales_RefreshGLPeriod != 'NULL'", oConn 'Open a recordset containing the year for which to pull data from Oracle
iYear = oRec.Fields("Year").Value 'Assign the year (e.g., 2006) to a variable
iMon =oRec.Fields("Month").Value 'Assign the Month (e.g., 10) to a variable
iStartYear = (iYear-2)
If iStartYear < 2009 then
iStartYear = 2009
End if
iZ=0
For iY = iStartYear To iYear
if iY Eqv iYear then
iCount = (iMon - 1)
Else
iCount = 12
End if
MsgBox(iCount)
For iX = 1 To iCount 'Loop 12 times to generate 12 SQL statements for 12 datapump tasks
if iX < 10 Then
sSQL= "SELECT b.Chain_code+'_inp' as Customer , " & Chr(13) & Chr(10) _
& " a.Product+'_it_inp' as Product, " & Chr(13) & Chr(10) _
& " 0 as Source, " & Chr(13) & Chr(10) _
& " 'Actual' as Category, " & Chr(13) & Chr(10) _
& " 'Metify_inp' AS DataSrc, " & Chr(13) & Chr(10) _
& " 'NoMode' as Mode, " & Chr(13) & Chr(10) _
& iY&"0"&iX&"00 as TimeId, " & Chr(13) & Chr(10) _
& "a.Pcenter, a.Account, Sum(CostRes) AS SignedData " & Chr(13) & Chr(10) _
& "FROM dbo.tblDistCost_Hist_"&iY&"_"&iX & " a, KW_Customers b " & Chr(13) & Chr(10) _
& "where a.customer=b.address_id " & Chr(13) & Chr(10) _
& "GROUP BY " & Chr(13) & Chr(10) _
& "b.Chain_code+'_inp',a.Product+'_it_inp', a.Pcenter, a.Account " & Chr(13) & Chr(10) _
& "HAVING SUM(CostRes) <> 0 " & Chr(13) & Chr(10) _
ELSE
sSQL= "SELECT b.Chain_code+'_inp' as Customer , " & Chr(13) & Chr(10) _
& " a.Product+'_it_inp' as Product, " & Chr(13) & Chr(10) _
& " 0 as Source, " & Chr(13) & Chr(10) _
& " 'Actual' as Category, " & Chr(13) & Chr(10) _
& " 'Metify_inp' AS DataSrc, " & Chr(13) & Chr(10) _
& " 'NoMode' as Mode, " & Chr(13) & Chr(10) _
& iY&""&iX&"00 as TimeId, " & Chr(13) & Chr(10) _
& "a.Pcenter, a.Account, Sum(CostRes) AS SignedData " & Chr(13) & Chr(10) _
& "FROMdbo.tblDistCost_Hist_"&iY&"_"&iX & " a, KW_Customers b " & Chr(13) & Chr(10) _
& "where a.customer=b.address_id " & Chr(13) & Chr(10) _
& "GROUP BY " & Chr(13) & Chr(10) _
& "b.Chain_code+'_inp',a.Product+'_it_inp', a.Pcenter, a.Account " & Chr(13) & Chr(10) _
& "HAVING SUM(CostRes) <> 0 " & Chr(13) & Chr(10) _
END IF
iZ = iZ+1
Set oPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_" & iZ).CustomTask 'Get a handle to the appropriate datapump task
oPump.SourceSQLStatement = sSQL
'Alter the datapump SQL statement
Next
Next
MsgBox(sSQL)
For iZ = iZ+1 to 12
'sSQL = "SELECT 0 as RC"
'Set oPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_" & iZ).CustomTask
'oPump.SourceSQLStatement = sSQL
Next
Set oPump = nothing
oRec.close 'Close the recordset object
oConn.close 'Close the connection object
Set oRec = nothing 'Release recordset object handle
Set oConn = nothing 'Release connection object handle
Set oPkg = nothing 'Release package object handle
Main = DTSTaskExecResult_Success
End Function
|
