• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

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.
0
dbbishop
Asked:
dbbishop
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://www.databasejournal.com/features/mssql/article.php/3372131

xp_cmdshell can run a command line tool
dtsrun is the command line tool to run a dts package

0
 
dbbishopAuthor Commented:
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
0
 
anyoneisCommented:
Maybe you need to delete the table first?

David
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please change the startup login account for the SQL Server Service to a NT Domain Account (with local permissions).
that should help
0
 
dbbishopAuthor Commented:
AngelIII: Are you talking about in the "exec master.dbo.xp_cmdshell 'dtsrun /SSROSSQL /Uuserid /Ppassword /NConvertFTEPositions'" statement?

anyoneis: That isn't the problem.
0
 
dbbishopAuthor Commented:
AngelIII: FYI, The server has direct access to the file. I can VNC into the server and see the drive, path and worksheet.
0
 
Anthony PerkinsCommented:
>>FYI, The server has direct access to the file. I can VNC into the server and see the drive, path and worksheet.<<
You mean you can login to the server and see the drive.  But that is not the point.  angelIII is suggesting that unless the SQL Server service startup account is a domain account you are SOL.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
Anthony PerkinsCommented:
Or go through Enterprise Manager: Management | SQL Server Agent | Properties and select the General tab.
0
 
dbbishopAuthor Commented:
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. :-(
0
 
dbbishopAuthor Commented:
FYI all-it is logging in as a local system account, so I guess, as acperkins out it, I am SOL!!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now