November 1, 2006 at 9:33 pm
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
--
http://www.zacharymcharris.com
The Men's Dump Survival Guide and Field Manual
http://www.mensdumpsurvivalguide.com
Finally, men have their own ten commandments and a five-step program for getting over and past getting dumped. A self-help manual filled with some comical insights regarding all the components of the dump, the dumper, the reasons, and what to do once it happens.
November 2, 2006 at 7:56 am
I think I have a simple solution for you, but I want to be sure that I know what you want.
Do you want the column name to be the filename, or do you want the filename to be a value in the column of the table?
November 2, 2006 at 9:50 am
Thanks for the follow up Will. I actually figured it out in that I just used this notation
oPKG.GlobalVariables("sFilename") = f1.name
and in the child package the DTSDestination... worked fine.
I have a book on DTS that actually encompasses VBSccript, but it doesn't really give enough "sensica;" detail for solutions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply