How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!