DTSrun/DTExe in sql server 2005

Question

Post reply

DTSrun/DTExe in sql server 2005

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 1

You can run it as a t-sql script with the following

exec master.dbo.xp_cmdshell 'dtsrun /S ServerName /E /NJobName'

¤ §unshine ¤

kristi.sargent Grasshopper Points: 22 More actions · Answer 2

kristi.sargent

Grasshopper

Points: 22

May 13, 2008 at 2:33 pm

#814798

thank u

rg-722805 Right there with Babe Points: 763 More actions · Answer 3

What version of SQL Server 2005 are you running? 64-bit?

Legacy DTS packages can be run using the DTSRun.exe, but in 32-Bit SQL Server 2005 only. See below links for more information and the work-around:

“Note: There is no 64-bit design-time or run-time support for Data Transformation Services (DTS) packages that were created in earlier versions of SQL Server.”

http://msdn2.microsoft.com/en-us/library/ms143706.aspx

http://msdn2.microsoft.com/en-us/library/ms141766.aspx

rg

kristi.sargent Grasshopper Points: 22 More actions · Answer 4

hi 32 bit I did get them to run via dtsrun and giving the proxy to run oscdmexec

thank you

janet macdonald-367964 SSC-Addicted Points: 412 More actions · Answer 5

I am having the same problem. I am still in SQL2000 and I cannot get the DTSRun command to work.

DTSRun /N {package_name} and I've tried DTSRun /N "{package_name}"

I've tried single quotes, double quotes, nothing works.

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 6

I have the same issue, however, to get it working in the meantime...

You can run it as a t-sql script with the following

exec master.dbo.xp_cmdshell 'dtsrun /S ServerName /E /NJobName'

¤ §unshine ¤

janet macdonald-367964 SSC-Addicted Points: 412 More actions · Answer 7

Thanks. Yes, you're right. I can get it to run that way. Now if I could just get it to run in a new job with DTSRun!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 8

Janet,

Try using double quotes and no braces around the package name. This is the DTSRun command I use in all job steps that execute DTS packages:

DTSRun /S "(local)" /N "FNBTS - HearingDateUpdate" /E

S = Server name

N = Package name

E = trusted connection (SQL Server agent runs as a Windows login; it's the login that executes the

package)

Greg

kristi.sargent Grasshopper Points: 22 More actions · Answer 9

the login needs to be able to do OS Commands. So u need to set up a proxy and credential that has perms to run the oscmdexec proxy

here are some instructions

http://blogs.msdn.com/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx

janet macdonald-367964 SSC-Addicted Points: 412 More actions · Answer 10

Hmmm. I have 'sa' as the owner; isn't that good enough. Also, when I schedule this package via the DTS wizard, it works fine with 'sa' as the owner.

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 11

Here is the working code for sql server 2k5

Execute master.dbo.xp_cmdshell 'dtsrun /S Server\Instance /E /N"Package Name"'

¤ §unshine ¤

tonyesc Newbie Points: 9 More actions · Answer 12

What's the protection level for your SSIS Package?

The default level is "protect sensitive with Key", my advice will be to change it to "Protect Sensitive with Password".

And add a password to your package. Run the deployment utility and create your deployment package as you wish, either File System or SQL Server.

Once deployed, schedule it on a Job and on the "command line" tab, edit the command line and add

/DECRYPT xxxx where xxxx is your password.

The SSIS Agent will use this key to decrypt sensitive information.

After you click ok to the job, when you open it again you can go to the command line tab and you will see that the Decrypt line is no longer "viewable". This is to protect the package from the SQL Agent side.

Good Luck.

Tony

Pura Vida!

maulikshah17 Valued Member Points: 67 More actions · Answer 13

I assume you have also installed SQL Server 2005 Backward Compatibility Compenents? This is required for running dts packages in SQL Server 2005

bart Old Hand Points: 363 More actions · Answer 14

Import ssis package into SQL server ie msdb after entering password from package creator.

Use Server security to simplify process.

homebrew01 SSC Guru Points: 55539 More actions · Answer 15

This is an 18 month old thread. Not sure that necro-posting has much value in most cases.

Wasted my time reading it until I noticed.