Prompt user to enter values

Question

Prompt user to enter values

arun8006

Valued Member

Points: 58
More actions
July 4, 2010 at 11:27 pm

#140899

Hello Gurus,
I have created a package with parameters, the package takes DB name1 and DB name2 and copies DBName1.TblA to DBName2.TableA, And these names are parameters, I have given DBName1 and DBName 2's values as the parameter values.
My requirement is when I run the package, I should get a prompt box where I can enter the DB names and the package should take those values and copies the content.
Is this possible? I think it should be. Can you please guide me to accomplish this.
Thanks you in advance
Arun

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

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

Alexander G. SSCrazy Points: 2705 More actions · Answer 1

First of all, SSIS is not a component that is supposed to have unser interaction, because it is a server technology, so I would not do this.

If you really want to have user interaction I suppose that there are a couple of ways to achieve this:

1. Implement a script component that shows a InputBox and write the result to a variable.

2. Call the package execution from a regular .NET program that handles the UI...

Alexander

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Alexander G. (7/5/2010)
First of all, SSIS is not a component that is supposed to have unser interaction, because it is a server technology, so I would not do this.
If you really want to have user interaction I suppose that there are a couple of ways to achieve this:
1. Implement a script component that shows a InputBox and write the result to a variable.
2. Call the package execution from a regular .NET program that handles the UI...
Alexander

Option 1 - I think you mean a script task, BTW - might be OK for testing, but really not good for a live solution.

Option 2 is the way to go - possibly calling a SQL Agent job which will execute the package (depending on where you want the package to execute).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

arun8006 (7/4/2010)
Hello Gurus,
I have created a package with parameters, the package takes DB name1 and DB name2 and copies DBName1.TblA to DBName2.TableA, And these names are parameters, I have given DBName1 and DBName 2's values as the parameter values.
My requirement is when I run the package, I should get a prompt box where I can enter the DB names and the package should take those values and copies the content.
Is this possible? I think it should be. Can you please guide me to accomplish this.
Thanks you in advance
Arun

Please, please, no.

Instead, create a config file for the packages, set the parameters you need as variables, then when you deploy the manifest you can have the user enter these details.

If the variables need to be changed, you can create an application that deletes this config file and rewrites it with the required changes - or you can redeploy the manifest.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I wuold like to echo what the other posters had to say, NO NO NO NO NO..

While testing in BIDS *maybe*, any other time, NEVER. If you need input use a package configuration of read the data from a table or file.

Do not under ANY circumstance build it to wait for user input.

CEWII

arun8006 Valued Member Points: 58 More actions · Answer 5

Thank you every one for the suggestions. That was really of great help!!

Now I have a package and the configuration files with the source and target DB information. The package executed fine.

Now, I want to customize this little furthur. The tables have information of all the users. Can i customize this package in sucha way that I take the user credentials and filter the records of only that user. I mean, if UserA runs the package, the data from DB1.TblA should be copy only the records of UserA to DB2.TblA.

Thank you

Arun

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

The answer is, it depends, how will the user be running it? This make a huge difference and many of the ways make it nearly impossible to do what you are talking about.

I'm wondering if you might be going about this the wrong way, why don't you explain what your package does and we can see what the options are.

CEWII

arun8006 Valued Member Points: 58 More actions · Answer 7

Here is what I am trying to do :

I have two applications say A and B. Application A uses DB_A and B uses DB_B.

A is like a master application which contains information of all the users, where as B is a subset of A which contains data related to only one user. So if there are 10 users then there will be 10 instances of DB_B.

Now, DB_A gets updated daily as from various sources. Now I want to create a application / Package for the users so that by passing only user ID their respective data should be copied from DB_A to DB_B (DB_B resides in the user's laptop).

As the tables are known, I thought of creating a package which takes User_ID as parameter and copies the related data from one database to another.

Hope I made my problem statement clear.

Thanks

Arun