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 and Excel 2003

We have a DTS package that imports data from a client's Excel file. Previously our client was using Excel 2000, but recently they upgraded to Office 2003, and now suddenly when I try to view the transformation task properties I get a Jet Database Engine error: "External table is not in the expected format".

Everything else with the file appears the same (layout, formatting, etc.).

Any suggestions?

I have the same problem. Did you ever received suggestions or a solution. I would be happy to have the answer.

Thanks

DTS and Excel 2003

We have a DTS package that imports data from a client's Excel file. Previously our client was using Excel 2000, but recently they upgraded to Office 2003, and now suddenly when I try to view the transformation task properties I get a Jet Database Engine error: "External table is not in the expected format".

Everything else with the file appears the same (layout, formatting, etc.).

Any suggestions?

I have the same problem. Did you ever received suggestions or a solution. I would be happy to have the answer.

Thanks