Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS import textfile AND inputbox data for just 1 column to add Expand / Collapse
Author
Message
Posted Saturday, December 29, 2007 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 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
Posted Wednesday, January 9, 2008 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2008 3:46 AM
Points: 5, Visits: 22
maybe not a newbie thing???
please help
Post #440471
Posted Wednesday, January 30, 2008 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2008 3:46 AM
Points: 5, Visits: 22
hmmm maybe moving this thread to another forum?
Post #449365
Posted Wednesday, January 30, 2008 3:14 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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
Posted Wednesday, January 30, 2008 4:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
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...?

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

R
Post #449735
Posted Thursday, January 31, 2008 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 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
Posted Thursday, January 31, 2008 12:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
how does the user kick off the dts? do they have access to enterprise manager?
Post #450137
Posted Thursday, January 31, 2008 1:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
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 »

Add to briefcase

Permissions Expand / Collapse