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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » DTS import textfile AND inputbox data for...

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

Rate Topic

Display Mode

Topic Options

Author

Message

jojonl73

Posted Saturday, December 29, 2007 5:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 3:46 AM
Points: 5, 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?

Post #437320

jojonl73

Posted Wednesday, January 09, 2008 2:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 3:46 AM
Points: 5, Visits: 22

maybe not a newbie thing???
please help Hehe

Post #440471

jojonl73

Posted Wednesday, January 30, 2008 7:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 3:46 AM
Points: 5, Visits: 22

hmmm maybe moving this thread to another forum?

Post #449365

Sreejithsql

Posted Wednesday, January 30, 2008 3:14 PM

Right there with Babe

Group: General Forum Members
Last Login: Saturday, August 25, 2012 9:48 PM
Points: 777, Visits: 134

hi
i agree with jojonl73 .

thx
sreejith

Post #449716

Bob Griffin

Posted Wednesday, January 30, 2008 4:00 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:26 PM
Points: 1,132, Visits: 663

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

Post #449735

jojonl73

Posted Thursday, January 31, 2008 12:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2008 3:46 AM
Points: 5, 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 ;)

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.

Post #450121

Bob Griffin

Posted Thursday, January 31, 2008 12:47 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:26 PM
Points: 1,132, Visits: 663

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

Post #450137

Bob Griffin

Posted Thursday, January 31, 2008 1:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:26 PM
Points: 1,132, Visits: 663

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

Post #450148

« Prev Topic | Next Topic »

Permissions