This is something that I was wondering how to do a couple of years ago, but now find myself in a crunch to get this done.

I am trying to assign a column in the destination table the value of the file that the data is coming from. So far, no luck, even with what I scanned so far online. Any help would be appreciated? Please email me directly as well. Thanks.

outer package

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Option Explicit

Function Main()

'Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim oPKG
Dim i, cn
Dim sFolder
sFolder = "e:\efi\"
Dim fso, f, f1, fc, s, fname
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sFolder)
Set fc = f.Files
For Each f1 in fc
Set oPKG = CreateObject("DTS.Package")
oPKG.LoadFromSQLServer ".", , , 256, , , , "test_text"
Set cn = oPKG.Connections("Connection 1") cn.DataSource = sFolder & f1.name
DTSGlobalVariables("sFilename") =f1.name
'fname = f1.name
'msgbox oPKG.GlobalVariables("sFilename")
oPKG.Execute
oPKG.Uninitialize()
Set oPKG = Nothing
Next
Main = DTSTaskExecResult_Success
End Function



'Inner package transformation

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("VendorNum") = DTSSource("Col001")
DTSDestination("vendorName") = DTSSource("Col002")
DTSDestination("OrderUOM") = DTSSource("Col003")
DTSDestination("CommCode") = DTSSource("Col004")
DTSDestination("poNum") = DTSSource("Col005")
DTSDestination("poLine") = DTSSource("Col006")
DTSDestination("PartNum") = DTSSource("Col007")
DTSDestination("Buyer") = DTSSource("Col008")
DTSDestination("CurrCode") = DTSSource("Col009")
DTSDestination("OrderDate") = DTSSource("Col010")
DTSDestination("RecDate") = DTSSource("Col011")
DTSDestination("PaidDate") = DTSSource("Col012")
DTSDestination("ReqDate") = DTSSource("Col013")
DTSDestination("InvoiceDate") = DTSSource("Col014")
DTSDestination("OrderQty") = DTSSource("Col015")
DTSDestination("RecvdQty") = DTSSource("Col016")
DTSDestination("ActPrice") = DTSSource("Col017")
DTSDestination("StdPrice") = DTSSource("Col018")
DTSDestination("CostCtr") = DTSSource("Col019")
DTSDestination("PoClosed") = DTSSource("Col020")
DTSDestination("PlantAdd1") = DTSSource("Col021")
DTSDestination("PlantAdd2") = DTSSource("Col022")
DTSDestination("PlantCity") = DTSSource("Col023")
DTSDestination("PlantState") = DTSSource("Col024")
DTSDestination("PlantZip") = DTSSource("Col025")
DTSDestination("Div") = DTSSource("Col026")
DTSDestination("VendorAdd1") = DTSSource("Col027")
DTSDestination("VendorAdd2") = DTSSource("Col028")
DTSDestination("VendorCity") = DTSSource("Col029")
DTSDestination("VendorState") = DTSSource("Col030")
DTSDestination("VendorZip") = DTSSource("Col031")
DTSDestination("AccptdQty") = DTSSource("Col032")
DTSDestination("RejctdQty") = DTSSource("Col033")
DTSDestination("PartDescription") = DTSSource("Col034")
DTSDestination("TotalAmountPaid") = DTSSource("Col035")
DTSDestination("DscntPerct") = DTSSource("Col036")
DTSDestination("DscntDays") = DTSSource("Col037")
DTSDestination("StockUOM") = DTSSource("Col038")
DTSDestination("UOMConv") = DTSSource("Col039")
DTSDestination("PPVGLAccountNumber") = DTSSource("Col040")
DTSDestination("PPVQty") = DTSSource("Col041")
DTSDestination("EntryDate") = DTSSource("Col042")
DTSDestination("filename") = DTSGlobalVariables("sFilename").value
Main = DTSTransformStat_OK
End Function

--