Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Posts
    41

    Unanswered: Importing files with unknown names

    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???

  2. #2
    Join Date
    May 2003
    Posts
    41
    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?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    as a matter of fact...just finished building that..

    Code:
    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_output,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%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2003
    Posts
    39
    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

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    I prefer using the fso object since xp_cmdshell presents problems.

  6. #6
    Join Date
    Aug 2003
    Posts
    39

    Thumbs up

    Yup

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rnealejr
    I prefer using the fso object since xp_cmdshell presents problems.
    Like what?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2003
    Posts
    39
    Memory Leaks

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by SHICKS
    Memory Leaks
    In stored procedures?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Most production sql servers drop xp_cmdshell because of security risks.

  11. #11
    Join Date
    Aug 2003
    Posts
    39
    http://www.databasejournal.com/featu...le.php/1580041

    This does not address memory leaks, but address issues of SP3 and XP_CMDSHELL.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by SHICKS
    Memory Leaks
    Seems to be the other way around...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •