Executing DTS Package - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Executing DTS Package

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-12-27 : 07:04:42

Hi Friends

I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file

DECLARE @Command varchar(1000)
declare @partcode varchar(100)

set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '

EXEC master..xp_cmdshell @Command

It Comes up with the Following Error

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)

Error string: Error opening datafile: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0

Error Detail Records:

Error: 3 (3); Provider Error: 3 (3)

Error string: Error opening datafile: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete.

NULL

(22 row(s) affected)

Vic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-12-27 : 07:05:36

Is there any better way of doing it in T-SQL
Thanks for your replies in advance

cheers
Vic

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 07:15:15

It seems that your package contains either some wrong path or the file is not where it should be. Does this package runs from EM? Why not go for BCP?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-27 : 08:01:52

Yes, there is another way also to run a DTS from SQL enviornment.

(a) Create DTS Package
(b) Create a Job to Run that Package
(c) use sp_start_job to start the JOB (and there by Package)inside a stored procedure
(d) Call the SP where ever u want.

there may be issues.... becuase the job can generally executes only by owner and super users. So u may need to add the users to SQLAgentOperatorRole in MSDB(if u r using SQL 2005). Read about this.

Madhu

svicky9
Posting Yak Master

232 Posts

Posted - 2006-12-27 : 10:12:10

hi Guys

Thanks for the Replies

I created a procedure using Dynamic SQL using BCP for exporting the files to the Text Format

I want to export it to C: drive on my machine but it exports to C: drive on the server

But the BCP works properly with the CMD Prompt

Is there any parameter i need to add?

Vic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 04:39:47

1 This forum is to post workable scripts and not to ask questions
2 You should post at DTS related Forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources