Using SSIS Execute Process Task to save command prompt output

  • am attempting to use SSIS in order to automate a very cumbersome process that we are currently using in our production environment.

    Due to audit concerns, we are not supposed to handle any sorts of passwords for any of the linked servers that we use on our production box.

    The process of getting the password is through a command line as following:

    pwecho <server_name> <user_id>

    When the above command is run from a command prompt, a password is returned.

    Currently, this process is handled in a Perl module which is darn time consuming.

    I am trying to replace the module with SSIS to be able to create a dynamic package that can accept a server name and a user id as input, get the password using an execute process task which uses the cmd.exe, store it in an package variable , pass it as input to an execute sql task which creates the linked server using server_name, user_id and the password from the variable.

    This is a long shot but if works can save a ton of time for us.

    Please help.

  • The answer is configuration files.

    I wouldn't use an XML file unless you can put it on a drive where no one else can access it. This seems to call for configuration values in a SQL Server table.

    Look up Package Configurations in Books Online. Look for the Environment Variables in a Table (or something like that). Then set up your package to use these tables and you should be all set.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 1 (of 1 total)

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