SQL Task Question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Task Question

Author

Topic

kwilliams

194 Posts

Posted - 2004-04-08 : 12:51:15

I've created a pretty cool package withing SQL Server 2000 that does the following:
1) Delete data in table job
2) Import job from txt file
3) Update table data to replace double quotes with empty spaces

This all works great, but I now want to add the following job to the beginning of the previously listed jobs. I want this job to delete the old "import.txt" file and rename the new "PSR*.txt" file to be "import.txt". "import.txt" is the file that the first SQL job pulls the data from (see above). This is what I've come up with:

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strLocation1 = "\\SERVER\FOLDER\SUBFOLDER\psr*.txt"
strLocation2 = "\\SERVER\FOLDER\SUBFOLDER\import.txt"
if objFSO.fileExists(strLocation1) then
objFSO.DeleteFile strLocation2 'delete old import.txt file if it exists
objFSO.RenameFile strLocation1,strLocation2 'rename PSR*.txt to import.txt
Set objFSO = nothing
End if

My questions are:
1) What "SQL Task" do I put this query under in SQL Server?
2) Is the syntax correct for the query above?

That should do it. Thanks for any & all help & advice.

KWilliams

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-08 : 12:55:48

Do you mean type of step inside a job? If so, then it should be an ActiveX script.

I would test the script before moving it into a job. Saving the code with an extension of .vbs. Then double click on your file and see if it does what you expect it to.

Tara

kwilliams

194 Posts

Posted - 2004-04-08 : 14:02:00

Thanks for the quick response tduggan. I've tested this script out on a VBScript page, but I want to do the same thing from a main package within SQL Server. I have 2 SQL Tasks with 2 Connections set up right now, and I want to add this job to the beginning of those. Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-08 : 14:16:29

When you say SQL Tasks, do you mean inside DTS packages? If so, just add this SQL task, then connect it to the other two with an on success connector.

Tara

kwilliams

194 Posts

Posted - 2004-04-08 : 14:24:14

Yes I'm meaning a SQL Task within a DTS package. Sorry that I wasn't more clear about that. I tried your suggestion, but received this error message:
Package Error
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Statement(s) could not be prepared.
Line 4: Incorrect syntax near '('.
Line 1: Incorrect syntax near '='.

For this code:

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strLocation1 = "\\dgcodmn1\dgfilesrv1\lems\reg_voters\psr*.txt"
strLocation2 = "\\dgcodmn1\dgfilesrv1\lems\reg_voters\import.txt"
if objFSO.fileExists(strLocation1) then
objFSO.DeleteFile strLocation2
objFSO.RenameFile strLocation1,strLocation2
Set objFSO = nothing
End if

I tried taking the parentesis off of strLocation1, but that didn't do anything. The error message just changed to:
Package Error
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Statement(s) could not be prepared.
Line 4: Incorrect syntax near 'strLocation1'.
Line 1: Incorrect syntax near '='.

Is there anything that's obviously wrong with the code I included? I've tested this script in a regular vbs page, and it worked. So I don't understand why it's not working as a SQL Server job.

KWilliams

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-08 : 14:26:14

In a DTS package, the object should be an ActiveX Script Task and not an Execute SQL Task.

Tara

kwilliams

194 Posts

Posted - 2004-04-08 : 14:43:19

Hi Tara,

I just changed that from a Execute Statement to an Active X Script with an "On Success" that's tied to the next job. But I'm now receiving this error message:
ActiveX Script Task: undefined
Error Code: 0
Error Source="Microsoft VBScript runtime error
Error Description: Object required: 'Server'

Error on Line 5

But when I parsed the query inside of the DTS package, it says that it was parsed successfully. Any suggestions?

ramdas
Posting Yak Master

181 Posts

Posted - 2004-04-08 : 15:29:42

Hi,
Try this in the activexscript task:
Set fso = CreateObject("Scripting.FileSystemObject")

Ramdas

Ramdas Narayanan
SQL Server DBA

kwilliams

194 Posts

Posted - 2004-04-08 : 15:34:02

Thanks for your input ramdas; it's greatly appreciated. It seems to have partially worked, but I'm still getting this error message: Invalid Task Result value.

I have no idea what that means. Any & all help is appreciated. Thanks.

kwilliams

194 Posts

Posted - 2004-04-09 : 12:39:26

I've changed the ActiveX job to Reg Voters, so the "unefined" has gone away, but I'm still getting the "Invalid Task Result value" error message when trying to run this job:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLocation1 = "\\SERVERNAME\FOLDER\SUBFOLDER\psr*.txt"
strLocation2 = "\\SERVERNAME\FOLDER\SUBFOLDER\import.txt"
if objFSO.fileExists(strLocation1) then
objFSO.DeleteFile strLocation2 'delete old import.txt file if it exists
objFSO.RenameFile strLocation1,strLocation2 'rename PSR*.txt to import.txt
Set objFSO = nothing
End if
End Function

...but when I parse this query within SQL Server, it says that it was successfully parsed. Any ideas? has anyone else seen this error message before?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-09 : 12:52:11

I think you need MoveFile instead of RenameFile:



Function Main()
	Dim objFSO, strLocation1, strLocation2
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	strLocation1 = "\\SERVERNAME\FOLDER\SUBFOLDER\psr*.txt"
	strLocation2 = "\\SERVERNAME\FOLDER\SUBFOLDER\import.txt"
	If objFSO.fileExists(strLocation1) then
		objFSO.DeleteFile strLocation2 'delete old import.txt file if it exists
		objFSO.MoveFile strLocation1,strLocation2 'rename PSR*.txt to import.txt
		Set objFSO = nothing
	End if
End Function

Tara

kwilliams

194 Posts

Posted - 2004-04-09 : 12:58:28

I figured out why I was getting that error message. I needed to add this info the query: Main = DTSTaskExecResult_Success

Now SQL Server says that the job is "successful", but the import.txt file never gets deleted, and the PSR*.txt file never renamed. I'm not sure how SQL Server can say that the job is successful if the query didn't actually do anything. This is how the query looks now:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLocation1 = "\\SERVERNAME\FOLDER\SUBFOLDER\psr*.txt"
strLocation2 = "\\SERVERNAME\FOLDER\SUBFOLDER\import.txt"
if objFSO.fileExists(strLocation1) then
objFSO.DeleteFile strLocation2 'delete old import.txt file if it exists
objFSO.RenameFile strLocation1,strLocation2 'rename PSR*.txt to import.txt
Set objFSO = nothing
End if
Main = DTSTaskExecResult_Success
End Function

I also tried your suggestion of changing that line to say:
objFSO.MoveFile strLocation1,strLocation2 'rename PSR*.txt to import.txt
...but I had the same results. Any other suggestions would be great.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-09 : 13:05:33

Try this out:



Function Main()
	Main = DTSTaskExecResult_Success
	Dim fso
	Set fso = CreateObject("Scripting.FileSystemObject")
	If fso.FileExists("C:\temp\import.txt") Then
		fso.DeleteFile "C:\temp\import.txt"
		fso.MoveFile "C:\temp\Somefile.txt", "C:\temp\import.txt"
	End If

End Function

Do not use the remote files that you have. Create the two files in my code on a local drive to see if the code works first. If it does, then use the remote files and see if it works. If it doesn't, then you need to figure out why the user can not see those files or delete files or rename files.

Tara

kwilliams

194 Posts

Posted - 2004-04-09 : 14:25:18

I just tried that task on my local computer, bit it didn't work either. So I don't think that it's a permissions thing. And if it was, wouldn't SQL Server give me an error message saying so? This is so confusing...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-09 : 14:54:22

The code that I posted works fine on my machine. I suspect that you need to install service pack 3a on the database server and on the client machine to fix the problem.

Tara

kwilliams

194 Posts

Posted - 2004-04-09 : 15:35:42

Tara,

Our server is running SQL Server 7.0, and it is up to SP4. I use SQL Server 2k on my machine, and it's up-to-date.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-09 : 16:30:16

That could be the problem, running a different version from the client machine than what the database server has. I know that it was a problem with different service packs in 7.0. Have you tried creating the DTS package from the server's console?

Tara

kwilliams

194 Posts

Posted - 2004-04-12 : 12:45:26

Hi Tara,

I hope that you had a great weekend. To answer your question, I haven't tried this job from the SQL console. But after doing some more testing, I think that I've figured out the problem. The MoveFile part of the script is not working for some reason. It says that it can't find the path to that file. But the DeleteFile part of the job works when I remove the if statement. From that, I think that the problem lies in the part that's supposed to rename the file from psr*.txt to import.txt. This is the code that produces this:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLocation1 = "\\SERVERNAME\FOLDER\SUBFOLDER\psr*.txt"
strLocation2 = "\\SERVERNAME\FOLDER\SUBFOLDER\import.txt"
if objFSO.fileExists(strLocation1) then
objFSO.DeleteFile strLocation2
objFSO.MoveFile strLocation1,strLocation2
Set objFSO = nothing
End if
End Function

So I guess my question is why can't SQL Server find strLocation1? I've tried putting a static name in for strLocation1, but it still didn't find the file. Then I removed the if statement, and the DeleteFile part worked, but the MoveFile part didn't. I think that the syntax is correct, so I'm really confused on why it's not working properly. Any advice is greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 12:48:15

I suspect it has to do with the name of the file. Is there really an asterisk in the file name?

Tara

kwilliams

194 Posts

Posted - 2004-04-12 : 12:51:51

No there isn't an asterick in the name. But I have some more good news. I just changed the name to a static filename again, and the job worked. So now I know for certain that the problem lies in the filename.

The problem with choosing a static filename is that the filename will always start with PSR, but the 5 numbers on the end of the name will change. Since there's only one PSR file at a time in that folder, I figured that I could use PSR*.txt. But I was wrong.

So is there a way to specify a file in the ActiveX Script that starts with PSR? If so, do you know what it is? Thanks again for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 12:55:57

I have this code:



Function Main()
	Main = DTSTaskExecResult_Success
	Dim fso, f, i, j, k, t, row, datetime_array, StartingKeyValue, StartingOtherValue, NewStartingKey, NewStartingOther, swap_pos, DimensionToSort, OtherDimension
	Dim MovedFile, NewFile, log, incoming, incoming_files, dir_count, willholddate
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set incoming = fso.GetFolder("f:\perflogs") 
	Set incoming_files = incoming.Files
	dir_count = incoming_files.Count
	ReDim datetime_array(dir_count, 1)
	' *** This loop will load the array with the DateLastModified file attribute ***
	For Each k in incoming_files
		datetime_array(i, 0) = k.Name
		datetime_array(i, 1) = k.DateLastModified
		i = i + 1
	Next 
	' *** This loop will perform the DateTime sort ***
	Const column = 1
	DimensionToSort = 1
	OtherDimension = 0
	For row = 0 To dir_count - 1
		StartingKeyValue = datetime_array ( row, DimensionToSort )
		StartingOtherValue = datetime_array ( row, OtherDimension )
		NewStartingKey = datetime_array ( row, DimensionToSort )
		NewStartingOther = datetime_array ( row, OtherDimension )
		swap_pos = row
	    	For j = row + 1 to dir_count - 1
			If datetime_array ( j, DimensionToSort ) < NewStartingKey Then
				swap_pos = j
				NewStartingKey = datetime_array ( j, DimensionToSort )
				NewStartingOther = datetime_array ( j, OtherDimension )
			End If
		Next
		If swap_pos <> row Then
			datetime_array ( swap_pos, DimensionToSort ) = StartingKeyValue
			datetime_array ( swap_pos, OtherDimension ) = StartingOtherValue
			datetime_array ( row, DimensionToSort ) = NewStartingKey
			datetime_array ( row, OtherDimension ) = NewStartingOther
		End If	
	Next
	' *** This is where the file gets set ***
	NewFile = "f:\perflogs\" & datetime_array(dir_count - 1, 0)
	MovedFile = "f:\perflogs\" & datetime_array(dir_count - 1, 0)
	Set f = fso.GetFile(NewFile)
	' *** Rename the file to the name that the Data Source expects ***
	If fso.FileExists("f:\perflogs\email\perf_mon.csv") Then
		fso.DeleteFile "f:\perflogs\email\perf_mon.csv"
	End If
	fso.CopyFile NewFile, "f:\perflogs\email\perf_mon.csv"
End Function

It iterates through a directory to find the newest file name and renames it to perf_mon.csv, which is the name of the file that my DTS package is expecting.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources