Solved

Alternatives to Running Scheduling DTS Packages with SQLSERVERAGENT

Posted on 2008-09-30
10
949 Views
Last Modified: 2013-12-19
We need some help figuring out how to run scheduled jobs (dts packages) without using the SQLSERVERAGENT as the user who runs the package.  We cannot use the agent because of security reasons beyond our control.  There has to be other simple ways to run this package.  

The DTS Package looks like this
Truncate --> OLE DB Provider for Oracle --> QA --> Load Table.

Hopefully that makes sense to someone because I'm a new to the database world.  
0
Comment
Question by:etan08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 13

Accepted Solution

by:
Wizilling earned 150 total points
ID: 22609394
to have your packages automated , you will need some sort of scheduler program. If you can not use sqlserver agent then either you can use the Windows System scheduler, or get one from the internet( free or pay )

the way you can run a DTS package through a DOS Command is using the command line utility DTSRUN

DTSRUN /S ServerName /N PackageName

Save the above in a  batch file (.bat) and have your chosen scheduler to run the above command.


Also, I have been in the sql server business for 5+ years now and have never encountered any security problems with sqlserver agent. If you can, please share your reasons why you dont what to use sqlserver agent. In my option it is a really good scheduling mechanism...
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22619819
We are using a product called Visual Cron to run SQL Jobs.  You can also run DTS Packages, SSIS Packages, Stored Procedures, and Queries.  It works much better then Windows Task Scheduler.  You can specify an account that you want to run each step of your visual cron job.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22623152
Can even run OSQL (or DTSRUN) jobs via a batch program which can be scheduled by Windows Scheduler. Also works for BCP if bulk loading data...

Is the problem with DTS - because you can write T-SQL code for anything DTS can do, or is it Scheduling out side of SQL Server ? Plenty of possibilities, but might need a bit more direction...

Visual Cron sounds promising as well - would be very handy to nominate a "secured" user account to run some of these types of things (is this the start of Unix invading MS ?)

Cheers,
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:etan08
ID: 22627812
Thanks for your suggestions so far, sorry for not being as active in this post as I should be...

Wiz, we cannot use the sqlserver agent because there was an issue previously and they will not allow us to use the agent with more permissions then it has now.  I'm not sure of the complete reason, that's just the gist.

Thor, if we can't get the dtsrun.exe to execute the scheduled task I will bring this up and see what they think about it, looks like a valid solution though. However, I think that the dtsrun will perform like we need it to. (I'll test it tomorrow)

Mark, it is a problem getting the DTS package to run under something other than sqlserver agent, I am interested in how to write T-SQL code for what the package executes (assuming the T-SQL code doesn't run the job in sqlserver agent).  Any learning resources I should look into for this specific purpose?
0
 
LVL 4

Assisted Solution

by:ThorSG1
ThorSG1 earned 50 total points
ID: 22628757
I have not done this before but this might help.  This is from help in sql.

dtsrun
[/?] |
[
    [
        /[~]S server_name[\instance_name]
        { {/[~]U user_name [/[~]P password]} | /E }
    ]
    {    
        {/[~]N package_name }
        | {/[~]G package_guid_string}
        | {/[~]V package_version_guid_string}
    }
    [/[~]M package_password]
    [/[~]F filename]
    [/[~]R repository_database_name]
    [/A global_variable_name:typeid=value]
    [/L log_file_name]
    [/W NT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]
]

You should be able to use something like this.

dtsrun /U user_name /P password /N package_name /G package_guid_string /V package_version_guid_string /W True

Theses values can be found in the msdb database in table sysdtspackages
package_name - Is the name of a DTS package assigned when the package was created.

package_guid_string - Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.

package_version_guid_string - Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID is assigned to the DTS package each time it is modified. The version ID is a GUID.

Please be sure to look at the package version.  Everytime you save a package it creates a new version record in the table.  According the the statement above you will want the first one created.

Again you can look in help to see some examples and help information.  You can also type from a cmd prompt: dtsrun /?.
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22628780
If you want to do it from OSQL you can also look in help for that command.  Since you already have the DTS package created dtsrun makes more sense.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22629951
There are several windows executable programs which can be launched fro the DOS prompt (using old fashioned term).

OSQL or DTSRUN (or BCP - and more) jobs can there be launched via a batch program which can be scheduled by Windows Scheduler. Also works for BCP if bulk loading data... They are considered part of the "engine" and so are available throughout every SQL server edition (except compact 3.5). In 2005 we also get sqlcmd which is meant to replace osql.

As a simple example for OSQL, which could also be DTSRUN as ThorSG1 shows above...



In windows, create a batch job (or cmd) : simple one liner, but would expect comments, versioning etc in "real life" e.g (between the rem's)
 
rem batch test to runs an osql script from dos
rem
"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" /E /e /n /ic:\mysql\jobs\UPDATE_RATE_CARD_END_DATE.sql /oc:\mysql\logs\UPDATE_RATE_CARD_END_DATE.log
rem
rem that is as easy as it can be
 
 
then simply create another text file, saving it with the extension sql (by way of convention) which has your T-SQL code...e.g. (between the -- )
 
--sql script to update the "end date" to be the end of day ie be a datetime finishing at 23:59:59
 
USE MYDB
go
IF (DB_NAME() <> 'MYDB')
BEGIN
  RAISERROR('Could not attach to MYDB database, aborting...',10,127)
  EXIT
END
go
 
select 'Started On : ',getdate()
go
 
update tbl_rate_card set end_time = dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(20),rca_end_time,106))))
GO
 
checkpoint
GO
 
select 'Ended On : ',getdate()
GO
 
-- that is all for now...
 
 
then all that has to happen is to run it - just like any other batch job. Or go to windows scheduler, new task, browse for the batch job, and schedule.

Open in new window

0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 50 total points
ID: 22629968
also the folder name in the example above has nothing to do with mysql it is more "my own sql stuff" , and the results of the run including the outputs of the select statements are visible in the log file - ie using the /o option.

For specific syntax, would need to look up Books on line - there is quite a few bits and options...
0
 

Author Comment

by:etan08
ID: 22670658
We had a problem coming up with the correct syntax that the dtsrun.bat should have.  To solve this issue we used dtsrunui.exe and that automated the process under advanced and automate.  I hope this helps anyone who is considering this option.  Thanks for the help everyone works like a charm.

Cheers
0
 

Author Closing Comment

by:etan08
ID: 31501719
Thanks for the help!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question