DTS global variables

Posted on 2006-05-24
Last Modified: 2013-11-30
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

Question by:dbaSQL
    LVL 13

    Expert Comment


    Here is an activex script from
    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
    LVL 30

    Accepted Solution

    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
    SET DateField = CONVERT(DATETIME,CONVERT(VARCHAR(8),DateField,112),112)

    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.
    LVL 17

    Author Comment

    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
    LVL 30

    Expert Comment

    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:

    LVL 17

    Author Comment

    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...
    LVL 17

    Author Comment

    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))
    LVL 17

    Author Comment

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

    Expert Comment

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

    Expert Comment

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

    Author Comment

    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
    END IF

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

         Main = DTSTransformStat_OK
    End Function

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now