SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Executing DTS Package		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svicky9
Posting Yak Master

United Kingdom
232 Posts
Posted - 12/27/2006 :  07:04:42  Show Profile  Visit svicky9's Homepage  Reply with Quote
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

United Kingdom
232 Posts
Posted - 12/27/2006 :  07:05:36  Show Profile  Visit svicky9's Homepage  Reply with Quote
Is there any better way of doing it in T-SQL
Thanks for your replies in advance

cheers
Vic
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 12/27/2006 :  07:15:15  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

madhuotp
Yak Posting Veteran

78 Posts
Posted - 12/27/2006 :  08:01:52  Show Profile  Reply with Quote
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
Go to Top of Page

svicky9
Posting Yak Master

United Kingdom
232 Posts
Posted - 12/27/2006 :  10:12:10  Show Profile  Visit svicky9's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 01/01/2007 :  04:39:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000