DTS Job Scheduling!! Very Confusing??

• SQLRep

  SSCrazy

  Points: 2052

  More actions

  November 21, 2006 at 8:29 am

  #173511

  Hi all,

  I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.

  I tried creating Credential and then to create proxy with this:

  Use master

  CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

  Use msdb

  Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'

  Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'

  Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'

  but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:

  Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.

  Thanks

• Dan Evans-311485

  SSC Enthusiast

  Points: 103

  More actions

  November 22, 2006 at 11:22 am

  #673547

  Ok...I'm no expert on credentials and proxies, but I have gotten this to work.  The issue is that there is encrypted secrets in your package.  Packages without passwords don't have this problem. 

  Here is what I do if I have to have a password in my package:

  1. I create a credential for the sql server service account, and a proxy for that.
  2. Log into the server using the service account, open the package, and enter the password information.  This will encrypt the password using the service account credentials.
  3. Import the job into the server.
  4. Add the job to a job step, and choose to "run as" your proxy name.

  I don't like this solution...but it does work for me.  I am chiming in here mostly because I want to see alternative strategies.

• mkbahrami

  SSC Veteran

  Points: 209

  More actions

  May 2, 2007 at 1:45 pm

  #703915

  Since I don't want to schedule every package for the devs, I was motivated to get this to work.  I used the same proxy user I had for SS2000 jobs (win authent).  Using SSMS, add the login (giving just dtsoperator in msdb permissions, no server permissions); create the credential (using the win authent login), create a proxy, giving 'SSIS Package Execution' and 'OS' subsystems.  The developers need access to run their jobs as the proxy user, so right-click your new proxy and add them as principals.

  Then, devs can 'create job', type='ssis', enter server, find the package (they have all been imported to msdb). Set 'run as' to your proxy user.  Devs need dts and job permissions on msdb.  Other posts here give great help on getting devs able to access SSIS on a remote server; I was able to get an AD group to work, so only needed one com/dcom config change per server.

  As to scripting, I'd use ssms once to get it to work, then script out what you need.

  HTH, Mary Bahrami