sql server programming: can you alter a package variable from within a stored procedure? -- sql server programming

can you alter a package variable from within a stored procedure?

BRLAdbaguy
8/21/2007 8:02:10 PM

sql server programming:

I'm only a few months into SQL 2005, but I'm at a stopping point with a small
project I'm working on. The objective is to allow testers to run packages
that can load sample data into tables without having to bother the developers.

So far, my solution is to deploy the packages to the file system, and set up
separate non-scheduled jobs for each package. The first one I have configured
is run by a job that has an external XML configuration and a 'command file'.
They set the values for the flat file connection manager and some
package-level variables. Then, in order for the user to run the job, they
open a query window and enter:
exec msdb.dbo.sp_start_job N'<name of the job' . This works fine as long as
the user is in the appropriate MSDB database role.

Everything is depending upon hard-coded values (flat file location and name,
load date for the fact table, etc.). Is there any way I can allow the testers
some flexibility through the method I've set up? It would be ideal to allow
them to run the job but over-ride one or more parameters. They will only be
connecting via SQL IDs since the machines are not part of the domain; this
prevents them from connecting to SSIS and running the package with 'set

Re: can you alter a package variable from within a stored procedure?

Dan Guzman
8/22/2007 7:22:24 AM

[quoted text, click to view]

You can override package properties on the DTSEXEC command line, just like
you can with config files. For example:

DTEXEC /FILE "C:\Packages\MyPackage.dtsx" /SET
"\Package.Variables[User::ImportFilePath].Properties[Value];C:\ImportFiles\MyImportFile.csv"

The "Set Values" tab under the SQL Server Agent SSIS Package step config GUI
can be used to change the generated command-line. Your testers will need
permissions to modify jobs with this method. If the job is run only on
demand, you might instead consider executing the package using
xp_cmdshell-->DTSEXEC from a stored procedure. That would allow you to
generate the desired DTSEXEC command based on the parameters specified by
your QA testers.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.