Post reply

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

  • December 29, 2007 at 5:12 am

    #181708

    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?

  • January 9, 2008 at 2:14 am

    #766051

    maybe not a newbie thing???

    please help :hehe:

  • January 30, 2008 at 7:08 am

    #773264

    hmmm maybe moving this thread to another forum?

  • January 30, 2008 at 3:14 pm

    #773553

    hi

    i agree with jojonl73 .

    thx

    sreejith

  • January 30, 2008 at 4:00 pm

    #773568

    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'

    R

  • January 31, 2008 at 12:09 pm

    #773894

    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.

  • January 31, 2008 at 12:47 pm

    #773908

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

  • January 31, 2008 at 1:12 pm

    #773918

    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:

    http://www.mssqltips.com/tip.asp?tip=1007

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

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