DTS from stored procedure.

This issue has come up in our office.
Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?
blindmanI think this one has been addressed before here (but I can't find it right now either). I think it basically involved using sp_start_job to start a job linked to the DTS task.

Regards,

hmscott

Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman|||http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&forumid=6&threadid=227

for your reference, I copy the code in the above link and post it here

the user executing this package must have execute rights to the sp_OA* sps
in master.

CREATE PROC <SP Name> as

DECLARE @.hr int, @.oPKG int

EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Loading the Package:
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS
Package Name>")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN
END

--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END|||Why don't you want to use xp_cmdshell?|||Originally posted by blindman
This issue has come up in our office.

Is there any way to call a DTS package from a stored procedure without jumping into xp_cmdshell to kick off DTSRUN?

blindman

the holy book also lists how to do the same from VB|||Thanks everybody.

blindman

DTS Executable error

We just installed SP3. The problem is that when I'm executing a DTS package
remotely from my client machine, it bombs when on the task that call an exe
cutable (error #128).
I don't get this error when I execute the DTS on the Server.
ThanksDavid,
If you can't install SQL client tools on the client machine, you're going to
have fun now.
Visit: http://www.sqldts.com/default.aspx?6,105,225,0,1
James Hokes
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:6529CE21-68FF-4394-B2F3-0C3AF9F93959@.microsoft.com...

quote:

---

> We just installed SP3. The problem is that when I'm executing a DTS

---

package remotely from my client machine, it bombs when on the task that call
an executable (error #128).

quote:

---

> I don't get this error when I execute the DTS on the Server.
> Thanks

---

sql

DTS Executable error

We just installed SP3. The problem is that when I'm executing a DTS package remotely from my client machine, it bombs when on the task that call an executable (error #128)
I don't get this error when I execute the DTS on the Server
ThanksDavid,
If you can't install SQL client tools on the client machine, you're going to
have fun now.
Visit: http://www.sqldts.com/default.aspx?6,105,225,0,1
James Hokes
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:6529CE21-68FF-4394-B2F3-0C3AF9F93959@.microsoft.com...
> We just installed SP3. The problem is that when I'm executing a DTS
package remotely from my client machine, it bombs when on the task that call
an executable (error #128).
> I don't get this error when I execute the DTS on the Server.
> Thanks

DTS and process task - doesnt wait my called exe application finish completely

Hi,

I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.

What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.

There is anyway to make a "while statament" to wait until my exe application finishes?

Any idea?

Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW

Hi,

Thanks for the help.

I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.

About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.

Thanks in adavance,
Tiago Teixeira|||Hi Teix

I was basically just thinking of something like this


DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END


Not sure how wise this approach is though

Good Luck

GW|||Hi GWilliy,

I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.

So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.

Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.

thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?

may be handy for future ref

Teix - Glad U got sorted

GW

DTS and ASP

Suppose i create procedure in DTS and I want to manage call of this
procedure from ASP code. How I can manage this. I have SQL 2000 and for me
both ASP and ASP.net are interesting\
Thank you
Execute a package from Active Server Pages (ASP)
(http://www.sqldts.com/default.aspx?207)

dts : call 2 procs within one execute sql task

Hi :

Can i call 2 procs within one task?
I have sp_proc1 ? (and have declared one global variable as input
parameter)
now i have another sp_proc2 which uses same input parameter

but if i write two statements like this within one task, i get an
error

exec sp_proc1 ?
exec sp_proc2 ?

I can solve the problem by writing them in 2 separate tasks, but would
like one task.

Please help..

thanks
Rashikarashika (rshivaraman@.ibs.com) writes:
> Can i call 2 procs within one task?
> I have sp_proc1 ? (and have declared one global variable as input
> parameter)
> now i have another sp_proc2 which uses same input parameter
> but if i write two statements like this within one task, i get an
> error

Standard question: what error is that? Even if the error appears to
be gibberish to you, it may not do to anyone else. (But since I don't
know DTS, it might be gibberish to me too. :-)

> exec sp_proc1 ?
> exec sp_proc2 ?
> I can solve the problem by writing them in 2 separate tasks, but would
> like one task.

You could always write a wrapping procedure:

CREATE MyWrapper @.s <datatype> AS
EXEC sp_proc1 @.s
EXEC sp_proc2 @.s

Note: the prefix sp_ is reserved for system stored procedure, and SQL Server
first looks for these in the master database. Do not use sp_ for your own
prodedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DtExec: setting user-defined properties with whitespace?

Hi there.

I'd like to call dtexec with something like this:

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;

I get an error along the lines of

Option "Source=localhost;Provider=blah;Integrated" is not valid".

How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.

Thanks,

Jon

JonB_QRM wrote:

Hi there.

I'd like to call dtexec with something like this:

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;

I get an error along the lines of

Option "Source=localhost;Provider=blah;Integrated" is not valid".

How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.

Thanks,

Jon

Double quotes.

dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;"Source=localhost;Provider=blah;Integrated Security=SSPI;"|||

Thanks for getting back so quickly. Your suggestion worked for a date string passed in as a property, but not a connection string.

When I run

dtexec /f package.dtsx /Set \package.variables[User::effectiveDate].Value;"2007-04-30 09:00" /Set \package.variables[User::connStr].Value;"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

I get the following complaint:

Argument ""\package.variables[User::connStr].Value;Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" for option "set" is not valid

It doesn't complain about the quotes around the date value.

Thoughts?

|||As an aside, if I write these props to a file and specify it using the /Com <filename> switch, dtexec takes them.|||

you may need a backslash to escape the double quotes.

Try this:

/Set \package.variables[User::connStr].Value;\"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"