DTS import textfile AND inputbox data for just 1 column to add

  • I'm quiet new with DTS and I managed to make an import with DTS from a textfile that append to a table X.

    Now I want to add one column for the rows that where imported. The value of that column should be given by the user that runs the DTS (it's a DTS on demand not by a schedule).

    Each time the DTS runs the input is different, but must affect to all rows that were imported in table X.

    For example: the user puts in the inputbox 'fineToday' and the last or first column in my table will be filled with 'fineToday' for all the rows that were copied to the table.

    How should I do that?

  • maybe not a newbie thing???

    please help :hehe:

  • hmmm maybe moving this thread to another forum?

  • hi

    i agree with jojonl73 .



  • I've just been working with DTS today on a very similar problem. Instead of prompting for input, i've been hard coding the column that I want added to the text file for each import. I only do this yearly with 3 inventory files, so no big deal. However, you maybe able to modify what I'm doing only slightly for user input. In the transform data task properties you'll want to use edit on the transformations tab. Here's my code:


    ' Visual Basic Transformation Script

    ' Copy each source column to the

    ' destination column


    Function Main()

    DTSDestination("loctag") = DTSSource("Col001")

    DTSDestination("item_id") = DTSSource("Col002")

    DTSDestination("disc") = DTSSource("Col003")

    DTSDestination("price") = DTSSource("Col004")/100

    DTSDestination("quant") = DTSSource("Col005")

    DTSDestination("mylocation") = "my hardcoded text"

    Main = DTSTransformStat_OK

    End Function

    I played around with adding an input box to this. It worked except I'm being prompted for input on each record.

    This isn't a complete solution, but maybe gets you on the right track...?:hehe:

    also...in books online search for 'using activex scripts in DTS'


  • it is maybe an idea to make 24 different dts script (each month 2) but I don't think you can call that ict 😉

    and when it's prompting for each line, ... , ... well that means that I have 5000 times the question... also not a good idea.

    Maybe another suggestion?

    off course I did some research in helpfiles etc, but I couldn't find the right suggestion yet.

  • how does the user kick off the dts? do they have access to enterprise manager?

  • you should be able to pass a parameter on the command line using dtsrun utility and the \A switch.

    Within the vb/activex code you reference the global variable like

    globalvar = DTSGlobalVariables("yourparmhere").value

    Here's an article on running a DTS package from command line:


Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply