Unanswered: Need to add filename to destination column in DTS using VBScript. Help!!!!!
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.
'Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim i, cn
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
'fname = f1.name
Set oPKG = Nothing
Main = DTSTaskExecResult_Success