Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Importing The Latest File In A Folder Via DTS Expand / Collapse
Author
Message
Posted Wednesday, April 06, 2005 9:26 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/djackson/importingthelatestfileinafolderviadts.asp


http://glossopian.co.uk/
"I don't know what I don't know."
Post #172696
Posted Tuesday, April 12, 2005 12:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 26, 2013 9:50 AM
Points: 100, Visits: 70

Nice, but I have to admit it seems a very complicated solution.  Circumstances are probably different but I used a bit of t-sql to do a very similar thing.  In my case so I could run a job that automatically identifies and then restores the latest backup of a database to a beta server.  It relies on xp_cmdshell and hardcoded folder locations which isn't great, but its fast and short. 

I've included the complete code below, at the very least it might provide someone with an alternative means that is easily integrated into a maintenance job or stored proc.

-- output a command shell directory listing to a text file, sorting by date

EXEC master..xp_cmdshell 'dir "\\share\FolderOfInterest\*.bak" /b /O-D > d:\output.txt', no_output
 
-- create a temporary table to store the directory listing
CREATE TABLE #tmpDirList (strFileName varchar(100))

-- bulk upload the directory listing to the temporary table
BULK INSERT #tmpDirList FROM 'D:\output.txt' WITH (DATAFILETYPE = 'char', ROWTERMINATOR = '')

-- create a variable to store the latest file to
DECLARE @strFileSource varchar(100)
SET @strFileSource = (SELECT TOP 1 strFilename FROM #tmpDirList)

 

 



Ryan
-----------------
www.quadrus.com
Post #174129
Posted Wednesday, April 13, 2005 10:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790

I agree, it is a complex solution, but each to their own

I restore a db each day using a similar trick, again using VB Script, probably because that's where my background is from.  Today (!) a workmate asked me how to do something simliar in T-SQL, and I was able to pass along your code as a good example.  Thanks

I'll have to write up the VB Script referred to above (runs on a File Print Server with no SQL installed) as my next article.

Dave J 




http://glossopian.co.uk/
"I don't know what I don't know."
Post #174472
Posted Thursday, October 20, 2005 11:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2008 2:43 PM
Points: 4, Visits: 2

Hi Guys,

 

I am a newbie so please forgive any ignorance to this subject. I am trying to Import the latest file from a separate server. I have tried to use the process doc provided and it work up to a point.

Each day a flat file is ftp'd into a folder. The file contains 8 days worth of records each day. This allows for any changes made after the data was originally created. I think I will need to add an sql script which would allow me to do this.

Do you have any ideas? I have only just started to learn sql and am afraid it may be out of my depth at the moment.

Thanks in Advance.

Regards,

 

Kal...

Post #231027
Posted Tuesday, October 25, 2005 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 26, 2013 9:50 AM
Points: 100, Visits: 70
not knowing much more about your scenario I would simply suggest if its an ftp location then use a DTS package.  its FTP object is very easy to work with for retrieving files.  if there's a server share instead then the T-SQL script may be slightly easier.  if the flat file is just rows of records then running a bulk insert statement from either a DTS package or a T-SQL script should be a piece of cake.

Ryan
-----------------
www.quadrus.com
Post #232169
Posted Tuesday, October 25, 2005 8:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2008 2:43 PM
Points: 4, Visits: 2

Hi Newbie,

 

Thanks for the information. I decided to use a DTS package and it is working except for one issue. I only want it to pick up the file for the latest date ie todays date. Right now it will still pick up the file with the latest date on it.

I am by no mean a VB expert but I think it has something to do with the below line of code.

 

If DTSGlobalVariables("FileName").Value <>  "" Then
        Main = DTSTaskExecResult_Success
    Else
        Main = DTSTaskExecResult_Failure
    End If

 

Any suggestions.

 

Post #232252
Posted Wednesday, October 26, 2005 5:16 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790

Replace the code in the  ActiveX Script with this:


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************


Function Main()
Dim filePreFix, folderName, fileName

folderName
= DTSGlobalVariables("FileFolder").Value
filePreFix
= DTSGlobalVariables("FilePrefix").Value

'Call the function, passing in the two Global Variables obtained above.
fileName = TodaysFile(folderName, filePreFix)
DTSGlobalVariables("FileName").Value = fileName

If DTSGlobalVariables
("FileName").Value <> "" Then
Main
= DTSTaskExecResult_Success
Else
Main
= DTSTaskExecResult_Failure
End If

End Function

'----------------------------------------------------------------------
Function TodaysFile(MyFolderName, MyFilePrefix)
'----------------------------------------------------------------------
Dim myFSO, HighestDate, MyResultName, myFolder, file
Set myFSO
= CreateObject("Scripting.FileSystemObject")

Set myFolder = myFSO.GetFolder(MyFolderName)
MyResultName = ""

HighestDate = dateAdd("s",-1,Date & " 00:00:00")
For Each file In myFolder.files
'Check to make sure the file starts with the right stuff
If UCase(Left(file.name,Len(MyFilePrefix))) = UCase(MyFilePrefix) Then
'Check last modified date
If file.DateLastModified > HighestDate Then
MyResultName
= file.path
HighestDate
= file.DateLastModified
End If
End If
Next
TodaysFile
= MyResultName
Set myFSO
= Nothing
End Function




HTH

Dave Jackson




http://glossopian.co.uk/
"I don't know what I don't know."
Post #232347
Posted Friday, November 09, 2007 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 09, 2012 2:21 PM
Points: 5, Visits: 30
Hi, this is a great article, thank you!!!
But is there anyway possible that you can do something with your screanshots so they could be viewed bigger. I would really like to take a look at all the tools that you are using there.
Thank you,
Vicky :D

Just to add, in case anyone is interested, (hint hint ;)), what I am trying to do is import data from a table into a file, which is easily done in DTS. My problem is that this transfer is supposed to take place every day and the file is supposed to be placed into a new folder generated each day. That is where I am stuck - creating that new folder in the same location each day and giving it a name increased by one from the already existing folder in that location. Ex.: fldrName1, next day - fldrName2, etc.
I have tried creating a Global Variable saved in the packet properties, but from there I am having a lot of trouble. If anyone can and is willing to help me out on this issue I would greatly greatly appreciate it.
Thanks again!
Post #420554
Posted Friday, November 09, 2007 11:58 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
Regarding the larger images, they used to work but the site upgrade appears to have killed them off. They are just standard DTS screenshots however. You can see more at my site where I reproduced the article.

Regarding naming a new folder every day, the following should help


/*

create some test folders
exec xp_cmdshell 'Md C:\TestDTS\Folder0001'
exec xp_cmdshell 'Md C:\TestDTS\Folder0002'
exec xp_cmdshell 'Md C:\TestDTS\Folder0003'
exec xp_cmdshell 'Md C:\TestDTS\Folder0004'
exec xp_cmdshell 'Md C:\TestDTS\Folder0005'

--try some later ones
exec xp_cmdshell 'Md C:\TestDTS\Folder0023'
exec xp_cmdshell 'Md C:\TestDTS\Folder0024'
exec xp_cmdshell 'Md C:\TestDTS\Folder0025'

--and again
exec xp_cmdshell 'Md C:\TestDTS\Folder0323'
exec xp_cmdshell 'Md C:\TestDTS\Folder1024'
exec xp_cmdshell 'Md C:\TestDTS\Folder5425'

*/
If Object_id('tempdb..#t1') > 0
Drop Table #t1

Create Table #t1 (
Outp Varchar(255))

Declare @path Varchar(255),
@folderPrefix Varchar(255),
@cmd Varchar(512)

Select @path = 'C:\TestDTS\'

Select @folderPrefix = 'Folder'

Select @cmd = 'dir ' + @path + @folderPrefix + '* /O:n /b' -- order by name, only return filenames

Insert #t1
Exec Xp_cmdshell
@cmd

Delete From #t1
Where Outp Is Null -- get rid of the blank row

Declare @num Varchar(4)

Select *
From #t1

Select @num = Right(Outp,4)
From #t1 -- recover last number

Select @num As [last known folder name]--show it

Declare @next Varchar(4)

Select @num = @num + 1

Select @next = Right('000' + @num,4)

Select @next As [next known folder name]--show it

Select @cmd = 'md ' + @path + @folderPrefix + @next

Select @cmd As [cmd to run]

Exec Xp_cmdshell
@cmd

--You can then do something like
Declare @outputFile Varchar(255)

Select Outputfile = @path + 'OutFile.csv'

Select @cmd = 'Move ' + @path + 'OutFile.csv ' + @path + @folderPrefix + @next

Select @cmd

--exec xp_cmdshell @cmd


This assumes the root folder is known, 'C:\TestDTS\ in this example and you have created a file in said folder named 'OutFile.csv' The final (commented out) xp_cmdshell call should move it into the newly created numbrered folder for you.

Paste all of the above into a SQL Task called after you have created the file, edit to suit your environment and you should be good to go.

Note that
I haven't tested this fully
The numbers are left padded with zeroes to sort properly
This should last at one folder per day for just over 27 years. :)

HTH

Dave J



http://glossopian.co.uk/
"I don't know what I don't know."
Post #420660
Posted Friday, February 15, 2008 12:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 02, 2008 2:39 PM
Points: 3, Visits: 4
Hi! Very good solution and easy to follow! :)
In fact, i was looking for something like this, but, the difference is that i want to import ALL the files in that folder, not only the latest. Also, i want to execute the DTS every hour, and i will import the files only if the files had change. is it neccesary to create a log file to manage this?How can do this DTS recursive????
ie: foldertest\file1.txt
foldertest\file2.txt

These two files are going to be updated automatically from other solution. Sometimes can be just one file, others two in the 'foldertest'. But i guess this is not that important, since we read all the files in the folder. right?
Any help is wellcome! Thanks a lot!
Post #456469
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse