SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jojonl73
jojonl73
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 22
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?
jojonl73
jojonl73
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 22
maybe not a newbie thing???
please help Hehe
jojonl73
jojonl73
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 22
hmmm maybe moving this thread to another forum?
Sreejithsql
Sreejithsql
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 134
hi
i agree with jojonl73 .

thx
sreejith
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 702
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
jojonl73
jojonl73
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 22
it is maybe an idea to make 24 different dts script (each month 2) but I don't think you can call that ict Wink

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.
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 702
how does the user kick off the dts? do they have access to enterprise manager?
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 702
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search