Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing The Latest File In A Folder Via DTS


Importing The Latest File In A Folder Via DTS

Author
Message
David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1879
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."
Ryan
Ryan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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 = '\n')

-- 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
David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1879

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."
Kal Elali
Kal Elali
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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...


Ryan
Ryan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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
Kal Elali
Kal Elali
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.


David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1879

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."
kissa49
kissa49
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 34
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 BigGrin

Just to add, in case anyone is interested, (hint hint Wink), 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!
David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1879
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. Smile

HTH

Dave J


http://glossopian.co.uk/
"I don't know what I don't know."
nefert
nefert
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Hi! Very good solution and easy to follow! Smile
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????Ermm
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search