asked on

DTS global variables

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

TommyTupa

Here is an activex script from http://www.sqldts.com/default.aspx?200
First a date is created. Then a filename is set based on that date. Then a source is set to that filename.

Function Main()
Dim oConn, sFilename

' Filename format - backup_yymmdd.txt
sFilename = "Backup_" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
' use this for global variable sFilename = DTSGlobalVariables("gv_MyPath").Value & sFilename & ".txt"
sFileName = "c:\temp\" & sFileName & ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbaSQL

ASKER

Well, nmc and tommy, i appreciate both of you getting back to me. i should have thought of that update on the date piece...definitely easy to do it afer the data has been loaded. as for the loop, well, i wish i had it far enough to explain. i do have it set up, i just haven't run it as it is pending the filename insertion.

i've got a big directory with .csv files. the 1st activeX checks to see if files exist, if so, it proceeds to define the gv's and then begins to move through the loop. of course, this is where each file is loaded, and this is where i'm stuck because i don't have the filename gv in place yet. i've read that i need the activex transform here from the gv into the table, but still very unsure how to accomplish. and then i'd only continue w/the loop if there is more than 1 file in the directory.

i should tell you both, i'm actually headed out of town in a few hours. i will be back over the weekend, though, and am anxious to get back to this as i really need to have it in place asap. i will let you both know where i'm at once i'm able to proceed. again, thank you both

nmcdermaid

So you are populating a global variable with a filename in your looping ActiveX code?

If that is the case, just do this:

1. Double click the data pump that is loading data from your file. You'll probably need to temporarily ensure the source file exists just to do this.

2. Go to the transformations tab

3. Select your destination field (on the right), press NEW and select ActiveX script

4. Press the properties button

5. Using the browser tab, build some code like this:

Function Main()
DTSDestination("FileNameColumnName") = DTSGlobalVariables("GlobalVariableContainingCurrentFilename").Value
Main = DTSTransformStat_OK
End Function

6. Press OK on the screen and test it!

PS: You'll need to have the source file and destination table all set up otherwise it will compain when you try and do this.

PPS: Here is a great site for looping in DTS:

http://www.sqldts.com/default.aspx?246

If you were to use this code then your ActiveX transformation would use this variable:

DTSGlobalVariables("gv_FileFullName").Value

dbaSQL

ASKER

well, i'm getting there, but i'm having problems w/the loop. also, the pacakge loaded one file in the directory, but it did not insert the filename into the appropriate column. instead, that column is a NULL.

now, the directories of files is a gv, so i know at least that one is working. like i said, it only did one file, though.
still working...

dbaSQL

ASKER

actually, the file it loaded had 23 rows. the table now contains 46 rows. 23 of which have NULL for the filename column. it inserted every row twice??

genID/Symbol/Weight/Update

NULL ADI .060760 2006-05-30 00:00:00
\\servername\shared\filequant\composition\XSD_ETF_Co ADI .060760 2006-05-30 00:00:00

any thoughts as to why this is happening?
the file name is XSD_ETF_Composition.csv

genID should be XSD_basket

i only accounted for the whole filename, i think, in the gv, as the gv is this: gv_FileFullName
and the value in the package is this: \\servername\shared\filequant\composition\XSD_ETF_Composition.csv

i am just giving it a starting point there. if it does the loop properly, it should keep walking through the files until none remain, correct?
also, fyi, t got the date just fine. i just put this default on the column def: (convert(datetime,convert(varchar(8),getdate(),112),112))

dbaSQL

ASKER

how can i change the whole filename strring to just everything preceding the first underscore in the filename?

nmcdermaid

It would be nice to know.... was this resolved by the poster or is it no longer an issue?

nmcdermaid

If we were not of assistance then by all means delete. But the thread seems to indicate that we were of assistance.

dbaSQL

ASKER

Actually, no, it wasn't of assistance, as I wasn't able to get beyond the NULL genID's. As I said, I was trying to load genID with the name of the filename being inserted. Like this: 'EMM_ETF_Composition.csv' would become 'EMM_basket', and be inserted into genID. I will go ahead and award points for the date suggestion - I am not doing an update after the file(s) are loaded, but I am using a similar construct as a getdate() default:

(convert(datetime,convert(varchar(8),getdate(),112),112))

The problem is resolved now, as I am using this:

Function Main()

dim sStart
dim iBackSlashReverse
dim sAfterBackSlash
dim sFinal
dim iUnderscore

sStart =DTSGlobalVariables("gv_FileFullName").Value

iBackSlashReverse = instr(StrReverse(sStart),"\")
sAfterBackSlash = Right( sStart , iBackSlashReverse - 1 )

iUnderscore = instr(1,sAfterBackSlash,"_")

IF iUnderscore > 0 THEN
sFinal = LEFT(sAfterBackSlash,iUnderscore -1)
ELSE
sFinal = sAfterBackSlash
END IF

DTSDestination("genID") = sFinal & "_basket"

Main = DTSTransformStat_OK
End Function