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

LVL 18
Who is Participating?
Regarding the date thing, you can run an update over the table afterwards to truncate the time part easily, its not necessary to do it while its importing.

UPDATE YourTable

Will truncate the time portions from the the field 'DateFiled' for every record in YourTable

Regarding the filename part, how exactly is your DTS looping over your set of files? What you need to do is:

1. Within your loop, when you have picked up the filename, assign this to a global variable
2. In your data pump in your transformations you need to assign the destination field with this global variable using an ActiveX transformation.

If you can explain to me how your loop works I can tell you what to do.

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
dbaSQLAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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:


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

dbaSQLAuthor Commented:
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...
dbaSQLAuthor Commented:
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??


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))
dbaSQLAuthor Commented:
how can i change the whole filename strring to just everything preceding the first underscore in the filename?
It would be nice to know.... was this resolved by the poster or is it no longer an issue?
If we were not of assistance then by all means delete. But the thread seems to indicate that we were of assistance.
dbaSQLAuthor Commented:
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:


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)
 sFinal = sAfterBackSlash

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

     Main = DTSTransformStat_OK
End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.