Automating using DTS - 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

New to SQL Server Programming

Automating using DTS

Author

Topic

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 15:19:11

I am trying to create a process to automate the FTP process and then IMPORT to a table. Once Import is complete, I would like to move the file from the FTP server and rename it. I tested the "commands" via CMD and it works. I then created a store procedure with the DOS commands to rename/move the source file. The command executes and deletes the file from the source, however, the copied files to a different destination disappeared. I did a search in my local hard drive(testing using my local folders), but can't find where the copied/renamed file went.

HEre is the portion of the commands:

SET @cmd ='Copy "C:\Documents and Settings\egllare\My Documents\BoxtestData.txt" "C:\Documents and Settings\egllare\My Documents\Archive\Copybox.%random%.bak"'
EXEC master..xp_cmdshell @cmd
GO

Can you please tell me what's wrong with this,

or direct me to a sample store procedure I can model..

TIA

Willie Llarena

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 15:53:19

What does .%random% do?

Try using abc instead of %random%

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 15:57:05

%random% simply generates a random number to be appended to the "new filename". This way I can preserved a copy of the file without getting wiped out the next time the package executes. It I use "abc", then it will create the same "filename.abc" everytime.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 16:03:46

I asked u to use abc for testing purposes only. Just put and c whether its working.If so the problem is with .%random%

OK run the following in Query Analyzer

Declare @cmd varchar(5000)

SET @cmd ='Copy "C:\Documents and Settings\egllare\My Documents\BoxtestData.txt" "C:\Documents and Settings\egllare\My Documents\Archive\Copybox.%random%.bak"'
print @cmd

and see the output

it is just %random% and not the number that u think generated randomly.
if u want a random name to be generated, u may need to use a function to do it and use that function in the string
eg.
Set @cmd = 'Copy "C:\....' + RndFileName() + '.bak"'

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 16:20:10

I executed the command via Query ANalyzer. I removed the %random% as you suggested. Here is the result..

In the output, It does not indicate "1 file(s) copied" as like in the command prompt.

Again, I cut and pasted the same command using CMD. It Worked fine. But, executing using Query Analyzer, NO copy took place.

Copy "C:\Documents and Settings\egllare\My Documents\BoxtestData.txt" "C:\Documents and Settings\egllare\My Documents\Archive\Copybox.bak"

The following file has been saved successfully:

C:\Documents and Settings\egllare\My Documents\SQL Stuff\sptest.rpt 140 bytes

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 16:35:15

quote:
Originally posted by Willie Llarena

I executed the command via Query ANalyzer. I removed the %random% as you suggested. Here is the result..
In the output, It does not indicate "1 file(s) copied" as like in the command prompt.

No Wonder.

What I did was to show u that the output is %random% , but not a random number as u expects. If u want to run the query u may need to create a function as per my answer in the bottom of the earlier one.

quote:
Originally posted by Willie Llarena

Copy "C:\Documents and Settings\egllare\My Documents\BoxtestData.txt" "C:\Documents and Settings\egllare\My Documents\Archive\Copybox.bak"
The following file has been saved successfully:
C:\Documents and Settings\egllare\My Documents\SQL Stuff\sptest.rpt 140 bytes

Which proves, my suggestion again.

-- U MAY NEED TO GENERATE THE RANDOM FILE NAME SEPERATELY --

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-14 : 16:44:13

You said you want to do a move but you are doing a copy.

What you are seeing is odd.
Is the server on your local machine? The c: in query analyser is on the server which could account for the copy not taking place.

For ftp, import, archive via tsql have a look at
http://www.nigelrivett.net/FTP/s_ftp_GetDir.html
http://www.nigelrivett.net/FTP/s_ftp_GetFile.html
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 16:52:56

"No Wonder.

What I did was to show u that the output is %random% , but not a random number as u expects. If u want to run the query u may need to create a function as per my answer in the bottom of the earlier one."

%RANDOM% has no bearing with the DOS command not functioning while executing it VIA query analyzer nor the stored procedure.

Your suggestion is fine. But I am not trying to create a random name for the "newFilename". I simply wanted to add a unique identifier to the newfilename to be created.

Even if I used your suggestion, it will not resolved the issue that the DOS "COPY" command was not being processed accordingly via Query Analyzer nor a EXEC sp_proc..

This is what I am trying to figure out. Did the "COPY" took place? If so, where did the output go. I can't find it anywhere..

When I execute the task via DTS Designer, it says "successfully executed the step".

Is there a log within DTS where I can see the actual result of the step that I executed? Or a log anywhere that it showed the execution of the DTS TASK completed or failed.

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 16:56:59

"You said you want to do a move but you are doing a copy.

What you are seeing is odd.
Is the server on your local machine? The c: in query analyser is on the server which could account for the copy not taking place.

For ftp, import, archive via tsql have a look at"

Thank you for the links NR.

I did changed the "MOVE" to "COPY" because everytime I tried executing the stored procedure, it DOES DELETE the soure file. SO, I have to create the source file everytime I have to re-test. Therefore, I changed it the command to "COPY" just for the sake of testing.

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-14 : 16:58:54

I forgot to mention, the SQL server is located in my LOCAL mahcine.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-14 : 17:05:07

Start with a simple command and build from there.
declare @cmd varchar(8000)
select @cmd = 'echo qwer > c:\a.txt'
exec master..xp_cmdshell @cmd
select @cmd = 'copy c:\a.txt c:\a2.txt'
exec master..xp_cmdshell @cmd

If that works then build on it until you find where the problem is.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Willie Llarena
Starting Member

7 Posts

Posted - 2006-03-15 : 11:11:01

Thanks for guidance, NR...

I was able to execute the stored procedure (@cmd) using a simple path to the file. It appears there is a limitation on the number of characters that can be used with the @cmd.

Here is the error message:

The Identifier ..... 'c:\ is too long. Maximum length is 128.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-15 : 11:55:33

That looks like a dos limitation on the file path rather than the @cmd variable - doesn't sound reasonable but.....
Check that you have closed the ".

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources