I want to write a DTS that will import a file every day. The problem is that the files is not named the same thing every day. There is a naming convention (SOMMDDYY.TRN) that it will follow. I want to import this file (which is a fixed width file) each day to a table (The table will be empty each day).
After it is imported, I want to look at the NAME of the file, and pull out the date portion of it. So, if the file is called SO122603.TRN, i want to pull out 122603, and then update my table with that date for every record. So when I am done, I will have a table that represents the file I imported, with one added column. This added column would be a Date/Time that has the date that was in the filename. How do I do this?I should add though - that this file that I want to import will be in a directory BY ITSELF. (There will be no other files in that folder). Is there a way to look in a directory, see what file is there, import it, and then parse the filename so I can get the date?|||as a matter of fact...just finished building that..
Select @.Command_String = 'Dir ' + @.FilePath + '\*.*'
Insert Into XLAT_Folder exec master..xp_cmdshell
PRINT 'DISPLAY XLAT_Folder'
SELECT * FROM XLAT_Folder
Delete From XLAT_Folder_Parsed
PRINT 'PARSE FOLDER'
Insert Into XLAT_Folder_Parsed (Create_Time, File_Size, [File_Name] )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As [File_Name]
From XLAT_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')
AND Substring(dir_output,40,(Len(dir_output)-39)) LIKE 'XLAT%'|||How bout adding and an Active X script Task that gets the file name and sets a global variable in the DTS package. Somewhere in the script just parse out the date or whatever.
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objFSO
Dim objFolder
Dim objFile
Dim oPKG
Dim fileName
Dim filePath
Dim oConnectionSource
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\data$\")
For Each objFile in objFolder.Files
filePath = objFile.Path
fileName = objFSO.GetFileName(objFile.Path)
Set oPKG = DTSGlobalVariables.Parent
oPKG.GlobalVariables("fileName").Value = (filePath & fileName)
Set oConnectionSource = oPKG.Connections("Text File(Source)")
oConnectionSource.DataSource = (filePath & fileName)
Set oConnectionSource = Nothing
Set oPKG = Nothing
Next
Main = DTSTaskExecResult_Success
End Function|||I prefer using the fso object since xp_cmdshell presents problems.|||Yup|||Originally posted by rnealejr
I prefer using the fso object since xp_cmdshell presents problems.
Like what?|||Memory Leaks|||Originally posted by SHICKS
Memory Leaks
In stored procedures?|||Most production sql servers drop xp_cmdshell because of security risks.|||http://www.databasejournal.com/features/mssql/article.php/1580041
This does not address memory leaks, but address issues of SP3 and XP_CMDSHELL.|||Originally posted by SHICKS
Memory Leaks
Seems to be the other way around...
