I am not a DTS expert, I do have a good idea of what I want to do, I am just not certain how to satisfy one piece of it. Long story short; once daily i have an agent job which invokes a package. the package goes out and retrieves a directory full of .csv files. every file is to be loaded, every file is of the same format and every file is loaded into the same table. I've got a number of other packages that do this just fine. this one is different for one reason.
Each of the .csv files containst two attributes: Symbol, Weight
Each file is loaded to a table with four attributes: genID,Symbol,Weight,Update
genID is to be created based on file name. for example, filename is: 'EMM_ETF_Composition.csv' genID becomes 'EMM_basket'
update is based on the inesrtion time
i understand i can just do a getdate() default on update. I would, however, like to do so such that it only contains the date, not the time. you know, MM/DD/YYYY
but, the genID. I believe I need to create a couple global variables in my dts package for this. one for the path of the files and one for the filename.
and i think i then need an activex transformation where i will set the filename field to be the gv. so, upon file insertion, the genID is created from the filename of the file being inserted.
i simply do not know how to do this gv part. i've been all over on line looking for samples/examples, and I'm just not finding it. i've got the package set up with the transform data task, columns 1 and 2 are mapped to Symbol and Weight. I've even got my two GV's in place. But, I don't know how to proceed w/the activeX transformation that is going to be required to account for the other two attributes: genID and Update
any advice at all is really appreciated