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

Function Main()

' These values were copied from the ADOVBS.INC file.
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

Dim oFSO		'Text File Source
Dim oFile		'Text File
Dim i, y, x
Dim ColumnName	'Array of Column Names
Dim DataLines()		'Array of each line of data

' Instantiate the ADO objects.
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

      myConn.Open "Provider=SQLOLEDB.1;Data Source=PCMCSQL;" & _
      "Initial Catalog=DataWarehouse;User Id=dwintranet;Password=molsoncanadian;"

'  instantiate the Scripting Object
    set oFSO = CreateObject("Scripting.FileSystemObject")

'   Open the file
    set oFile = oFSO.OpenTextFile("\\Hydlna01\downloads\SQLDWN\FPMMATXU.TXT")

'  store the first line, which is the ProductCode Names, in a global variable
'    DTSGlobalVariables("StartDate").value = x.Readline
	ColumnName = Split(oFile.Readline, "\")

'    MsgBox "The Start Date is: " & DTSGlobalVariables("StartDate").value
'	MsgBox ColumnName(0)

	i = 0  
	Do While not oFile.AtEndOfStream
		ReDim Preserve DataLines(i)
		DataLines(i) = oFile.Readline                  'Each line of data put into array
        		i = i + 1  
	Loop

	oFile.Close
'  Good practice to setobject variables to nothing
	Set oFSO = Nothing
	Set x = Nothing


'  store the second line, which is the End Date, in a global variable
'    DTSGlobalVariables("EndDate").value = x.Readline
'	DataLine = Split(x.Readline, "\") 
'   MsgBox "The End Date is: " & DTSGlobalVariables("EndDate").value
'	MsgBox DataLines(2)


 strSQL = "Delete DataWarehouse.dbo.MattMatrix"
    myRecordset.Open strSQL, myConn, adOpenKeyset 
    
    For x = 0 To UBound(DataLines) Step 1
        Data = Split(Trim(DataLines(x)), "\")
        For i = 3 To UBound(Data) Step 1
            If Data(i) = "X" Then
                Data(i) = ColumnNames(i)
            End If
        Next 
    
        For y = 3 To UBound(Data) Step 1
        If Data(y) > " " Then
            strSQL = "INSERT INTO DataWarehouse.dbo.MattMatrix (SoldToCustomerNumber, ShipTo, CustomerType, ProductCode)  Values  ('" _
                    & Data(0) & "', '" & Data(1) & "', '" & Data(2) & "', '" & Data(y) & "') "
             myRecordset.Open strSQL, myConn, adOpenKeyset 
        End If
        Next 
    Next 

	Main = DTSTaskExecResult_Success
End Function