Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!