I badly need to insert filename along with data in DTS and am losing the battle

Question

I badly need to insert filename along with data in DTS and am losing the battle

tibetandog

SSC-Addicted

Points: 419
More actions
November 1, 2006 at 9:34 pm

#97889

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

dmbaker SSCertifiable Points: 5139 More actions · Answer 1

I've encountered problems when trying to set variables of a package that I'm executing, wonder if you're having the same problem? There is a problem in DTS that surfaces when dealing with package variables, such that you must delete and then re-add the variables to the package. It looks like you might be having the same problem here.

You might try, in your Main() code, deleting the sFileName variable from the package, and then re-adding it with code. IIRC, the Package object has a "GlobalVariables" collection with a Remove() method...and there's a AddGlobalVariable() method that lets you re-add a variable back to the collection. Basically, Remove() the variable (by its name) and then just AddGlobalVariable() to put it right back with the value you want.

Sorry I don't have any more info, but check Books Online for the above methods. I have code but it's in C# so don't know how helpful that'll be.

tibetandog SSC-Addicted Points: 419 More actions · Answer 2

Thanks. I forgot to delete this from the system, but I solved it.

oPKG.GlobalVariables("sFilename") worked