Run SSIS package from SQL client

views:

286

answers:

Run SSIS package from SQL client

I deployed my working package on server which is enterprise edition, SSIS installed on it. When I tries to run package by connecting to integration services engine from my desktop SQL client (which doesn't have SSIS installed) I get error "The task "Send Mail Task" cannot run on this edition of Integration Services. It requires a higher level edition." Does it mean that I need to login to the server (RDP) and then run the package?

Also, when I schedule the package thru SQL agent it fails saying login time out but my windos auth login works for everything from connecting, deployment. Any clue?

+2 A:

For your first problem - yes, you need to RDP into the server in order to use SSMS to start the package. When you start it using SSMS on your client, it's attempting to launch the DTExec process on your client machine. It's not running DTExec on the server.

Your second problem is likely a permissions issue. Possibility #1: The connections you have set up on your package require your authentication information, and they don't have it because they're running as the Agent account. You can fix that by creating a Proxy for your account and using that to run your job step. Possibility #2: The connections you have set up on your package are having their sensitive information stripped out due to the default encryption on the packages that prevents anyone but "you" from seeing it - including a SQL Agent job that isn't running "as you". The same resolution as above can help that (as well as others).

Todd McDermid 2010-03-23 06:33:03

Thank you Todd for clarification and solution.

Pramodtech 2010-03-23 06:51:43

Do I need to RDP even for scheduling/executing job?

Pramodtech 2010-03-23 06:56:38

No, a scheduled job's execution location is by default the machine it is scheduled on.

William Todd Salzman 2010-03-24 14:16:06

I can run packages from RDP successfully. Only problem is with SQL job calling packages. I use Windows authentication but for safer side I changed protection level "EncryptSensitive" to "DoNotSaveSensitive" and deployed all packages but still same login timeout error. I created proxy account but here I get error "Error authenticating proxy Domain\MyUser, system error: Logon failure: unknown user name or bad password". I know that user exist and password is correct. Don't know what is messed up.

Pramodtech 2010-03-23 10:02:30

Don't save sensitive removes stored passwords etc from the package. If you use this, then you have to provide the passwords to the package somehow. Usually this will be through a package configuration. See other questions on StackOverflow about how to use package configurations to set up your connections.

William Todd Salzman 2010-03-23 11:03:47

But I use windows authentication so I think there is no sensitive data also package runs fine directly. I tried with package configuration as well but no success. I think there is some permission issue.

Pramodtech 2010-03-24 10:18:17

My package runs fine directly but when I schedule a job it fails with error "LoadFromSQLServer method has encountered ... Login timeout expired". I tried using proxy account but in that case error is "Proxy authentication failed... Unknown user/pwd"

One strange thing I noticed is in log it says "job executed as user Domain\Svc_Directory" but my user name is different. What is this Svc_directory user and how it is trying to execute job when I'm the owner of job.?

Is there clean way of scheduling package?

Pramodtech 2010-03-24 12:06:02

When a job is executed by the scheuler (Sql Agent) it runs under the user name of the user that is defined in the Sql Agent Service. The owner of a job does not matter when the job is being run, the account that initiated the job is the one who is running the job. That is also why your login is failing. If your password is not in your connection string in your package config, then it will try to use the authentication of the account that executed the job. Your choices are 1) Add password to your connection string in your config, or 2) give the Domain\SVC_Directory appropriate permissions

William Todd Salzman 2010-03-24 14:13:14

Choice number two from my other comment is almost never the right choice. By the way, since you accepted the original answer and this is a new question that was raised by the answer you selected, it is allowable to start a new question rather than use the answers section to try to get clarification.

William Todd Salzman 2010-03-24 14:15:05

ansaurus

tags:

views:

answers:

Run SSIS package from SQL client

related questions