asked on

I want to run a DTS package in T-SQL

Okay all, I'm pretty dense with SQL Server (2000) outside of just regular T-SQL for select, inserts, updates, etc. I've done some stuff using cursors (wowee!) and have created tables, etc., but all this other stuff like creating and scheduling jobs, MTS, DTS, whatnot.

I am working on a conversion process to bring some data from Excel into SQL Server. I have struggled through help files, etc., enough to local DTS package (ConvertFTEPositions) that does the conversion.

I have a T-SQL procedure I run after the DTS package that goes through and does a lot of work with the data that was just loaded. It is an ongoing process. Is there some way (and I need specifics) to execute the DTS package from within the stored procedure. Will the execution of the package be synchronous? In other words, I want to wait until execution of the package finishes before continuing with the steps in the procedure?

Thanks.

p.s. I'm sure it is a simple question for some of you experts, but for me it might as well be on how to drill for oil on the moon. Lots of points up for grabs.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

It seems to run, but I get the following output. If I just right-click on the package from SQL Server Enterprise Manager, and select Execute, it runs fine. However, using the statement "exec master.dbo.xp_cmdshell 'dtsrun /SSROSSQL /Uuserid /Ppassword /NConvertFTEPositions'" (of course real username and password), from QA, I get the following, and the table does not get loaded. SQL Server does have access to to the Excel spreadsheet)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from 'OSH 494$' to [HumanResourcesTest].[dbo].[tblPositionConvert] Step
DTSRun OnError: Copy Data from 'OSH 494$' to [HumanResourcesTest].[dbo].[tblPositionConvert] Step, Error = -2147467259 (80004005)
Error string: Failure creating file.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003436

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54)
Error string: Failure creating file.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003436

DTSRun OnFinish: Copy Data from 'OSH 494$' to [HumanResourcesTest].[dbo].[tblPositionConvert] Step
DTSRun: Package execution complete.
NULL

anyoneis

Maybe you need to delete the table first?

David

Guy Hengel [angelIII / a3]

Please change the startup login account for the SQL Server Service to a NT Domain Account (with local permissions).
that should help

D B

ASKER

AngelIII: Are you talking about in the "exec master.dbo.xp_cmdshell 'dtsrun /SSROSSQL /Uuserid /Ppassword /NConvertFTEPositions'" statement?

anyoneis: That isn't the problem.

D B

ASKER

AngelIII: FYI, The server has direct access to the file. I can VNC into the server and see the drive, path and worksheet.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

Is the Drive a local drive or a mapped drive?

anyhow, please try to do the change I suggested:
go to the control panel, administrative tools -> services

Anthony Perkins

Or go through Enterprise Manager: Management | SQL Server Agent | Properties and select the General tab.

D B

ASKER

It will be Monday before I can check this out at work. If it is set at something other than what it needs to be, I doubt I will be allowed to make any changes, since it is a production server. I would hate to be the cause of bringing down the whole system. :-(

D B

ASKER

FYI all-it is logging in as a local system account, so I guess, as acperkins out it, I am SOL!!