473,574 Members | 2,459 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Execute DTS Packages

I have created a functioning DTS package inside Enterprise Manager and
now I want to be able to execute it outside of EM. The package imports
data from an SQL dbase to a Visual FoxPro dbase. I would like this DTS
package to execute everytime an Insert(of certain critera) is made on
the SQL dbase. I am thinking that using an Insert Trigger on the SQL
table is the way to go about this. I am seeking advice as to... is this
the best way to go about this.

Also, and if so, in a trigger which route is the best to take...
1. use dtsrun command line utility or
2. setup a COM object to run the DTS package

I have tried both in SQL query analyzer and am having troubles with the
syntax. Could anyone possibly send the code for both ways. Thanks.
Good Day to all,

Brett

Jul 23 '05 #1
10 8618
Its not clear why you would want to do this... What are you doing in
the DTS that you cant do thru normal trigger? It would help if you can
explain more.....

Not sure if you can run the package thru trigger.. May be set up a job
and invoke a job... I am also curious....

Jul 23 '05 #2
I have tried linking SQL database to the VFP tables with T-SQL and have
had no luck. I have tried for weeks to link the two via Remote Servers
or T-SQL and working with directly in triggers. So far this is what I
have been able to get to work. If you have other ideas or any help
would be appreciated.

Brett

Jul 23 '05 #3
You should be able to fire up DTSRUN using sp_oacreate in your trigger.
Have you tried this?

Having a job that calls the DTS is another option, then start the job
in your trigger with msdb..sp_start_ job or something like that.
I have done both of these.

Jul 23 '05 #4
Yes I have tried this without sucess. The package executes and works
properly when I run it manualy in EM. However, when I place the code
listed below in SQL Query Analyzer it does not execute. The code
parses fine, no errors and when it is executed QA returns 'completed
the command successfully', but nothing has happened. Here is the
code...

DECLARE @object int
DECLARE @hr int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @hr = sp_OAMethod @object,
'LoadFromSQLSer ver("ShannonPC" , "sa", "", 256, , , ,
"MiTekTransfer" )', NULL
IF @hr <> 0
BEGIN
print 'error LoadFromStorage File'
RETURN
END

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

Any ideas as to why this is not running my DTS package.

Thanks

Jul 23 '05 #5
This code shows an example, I have used it before with success:

http://www.databasejournal.com/featu...le.php/1459181

The first problem I see is, your LoadFromSQLServ er parameter "256" is
incompatible with passing in "sa" as your username. "256" means
DTSSQLStgFlag_U seTrustedConnec tion, which says to use a trusted
connection. So my guess is that you're having a permissions problem.
Change the "256" to "0" and try again.

I am surprised that you got no error message back from sp_oacreate
though...

Gary

Jul 23 '05 #6
Yes, I recongized this issue after I made the post and did some further
searching. If I use win. auth. in the package I set parameter to 256,
with no user or pass, and if I set SQL auth. in the package I set
parameter to 0 with user sa. I have tried both with the same
results... no errors and no execution.

Jul 23 '05 #7
Do you have DTS package logging turned on? Can you tell it's not even
starting the package?

Try just starting DTSRUN.exe from a command line, and see if that
works.

sp_oacreate may be disabled as a security measure, but if it was I
would expect that you would get an error back.

Can you try putting a dtsrun.exe call in a job? Run the job and see if
it starts the DTS package. If not you have bigger problems.

If it works in the job, use msdb..sp_start_ job to start the job from
the trigger.

Jul 23 '05 #8
It seems that the issue might be with connecting to a Visual Fox Pro
table outside of the DTS program. The package which transfers from SQL
to VFP works in DTS but not in SQL Query Analyzer. I setup up another
dts package which transfers the same data but from SQL to a text file.
This package executes properly in DTS and SQL QA. It operates under
the same code as posted above, with changing the package name. The VFP
table are on the local machine though.

I also tried exec master..xp_cmds hell 'dtsrun /Sshannonpc /E256 /
Npackagename. This executes the package with the text file but not the
VFP tables. I get the error 'Invalid class string.' However, no
errors occur when runing the package manually in DTS. Ideally I would
like to import right into the VFP tables and not have another app that
reads from the text file then to the VFP tables. ???

Jul 23 '05 #9
When you say "The VFP table are on the local machine", what do you
mean? On your local desktop machine, or on the database server?

Sometimes people get local/server paths mixed up. The path to the VFP
needs to be reachable via the server, not your local PC. Is this the
problem?

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6813
by: Sheila King | last post by:
I have searched for an answer to this question on the comp.lang.python archives at Google Groups, and also looked through the (sparse) MySQLdb documentation, and cannot confirm for a FACT what I think I know. Looking for affirmation before I go "assuming" something and getting myself into trouble somewhere along the line... Here is what I...
2
5038
by: Tim Williams | last post by:
I'm trying to write a simple python program to access a MySQL database. I'm having a problem with using MySQLdb to get the results of a SQL command in a cursor. Sometimes the cursor.execute works, sometimes not. From mysql: mysql> show databases; +-----------+ | Database |
2
2909
by: Rich Tasker | last post by:
My goal is to execute a DTS package that calls multiple child DTS packages from a C# (2003) app and display the progress of the entire process to the user. I have followed the model defined in this knowledge base article. ( http://support.microsoft.com/?id=319985 ) to execute a DTS package and monitor it's events. I'm not able to capture...
2
4563
by: Matthew Wells | last post by:
Hello. How do you execute an existing SQL DTS package from a command button on an Access form. I am not going to use Access data pages, just a standard form. Thanks. Matthew Wells MWells@FirstByte.net
3
5095
by: Peter Afonin | last post by:
Hello, Our SQL server used to run under System account, and I had no problems executing DTS packages from the ASP.NET: Dim oPkg As DTS.Package oPkg = CreateObject("DTS.Package") oPkg.LoadFromSQLServer("WIN2000", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "Import DPCK")
1
2929
by: rAinDeEr | last post by:
Hi, I have started using DB2 UDB ESE v8.2 in Linux recently. I have seen a lot of articles on packages, prepare statement, bind and execute statement. I havent got a clear idea. I have written a few stored procedures but havent used prepare and execute.
0
1665
by: giovanni gherdovich | last post by:
Hello, first of all: Is this the right place to ask plastek-related questions? I'm trying to make plastex work on my Ubuntu Dapper Drake. For LaTeX, I have the all-in-one package tetex.
3
4930
by: joe jacob | last post by:
I configured apache to execute python scripts using mod_python handler. I followed below mentioned steps to configure apache. 1. In http.conf I added <Directory "D:/softwares/Apache2.2/htdocs"> AddHandler mod_python .py PythonHandler mptest PythonDebug On </Directory>
3
3009
by: =?Utf-8?B?THVpZ2k=?= | last post by:
Hi all, how can I execute a SSIS Package from an aspx page in C#? I'm using .NET 2.0. Thanks a lot. -- Luigi http://blogs.dotnethell.it/ciupaz/
0
7715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8041
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7806
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8093
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6440
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3748
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2233
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1053
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.