Hello,

When I designed and deployed DTS packages, I often stored them in SQL Server
or in the file system and ran them from batch scripts (using DTSRun.exe) or
from ASP.NET applications (either case, most of them weren't run at the SQL
Server itself).

I was hoping I could do the same with SSIS, but it seems you can't run SSIS
packages from servers without SSIS installed, and SSIS requires a SQL Server
license... Since I'm stuck with a single SQL Server, the implications of
this is that I have to run the SSIS packages from my SQL Server.

I'm not happy about the prospect of running batch scripts and ASP.NET
applications on the SQL Server, so I was hoping there is some way I can
execute the packages using T-SQL or something similar? I need to start the
SSIS packages from batch scripts and ASP.NET applications, and I also need
to set variables in the packages before I run them...

Any suggestions? I'm interested in hearing any best practices here!
Performance-wise, I don't mind that the SQL Server needs to run the packages
itself, and I'm sure in the end, I'll find a robust and flexible way of
running the packages from within SQL Server... I just need some help getting
there :-)

Thanks!


Regards,
Nils Magnus

exec xp_cmdshell 'dtexec /File c:\Packages\MyPackage.dtsx /set
\package.variables[MyVariable].Value;MyValue'

See dtexec documentation in BOL. Obviously there will be security
considerations in using xp_cmdshell.

Charles Kangai, MCT, MCDBA

"Nils Magnus" wrote: