Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: DTS Pakcage File Import but Filename Changes

    Hi All,

    I am trying to automate a rather complicated file import routine using a DTS package. I'm getting on ok with it but have now reached the limits of my knowledge.

    At the moment I am sent a file every month e.g. "perf04-05m1.csv". I then rename it to "import.csv" and run the DTS package. I would like to make the process more dynamic so that the the DTS package takes in the most recent file no matter it's filename e.g. I don't have to re-name the file to "import.csv" in order for the DTS package to run.

    I have had a look at BOL and think that I need to use either a Dynamic Properties Task or a Global variable but I'm not sure how to tie it all together, can anyone offer any pointers or know of any good articles I can have a look at. I may also need to dynamically alter some of the SQL statements within the DTS package as well.

    Cheers

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you do this in a sproc?

    And then bcp the data...I also make sure I archive everything in the folder to a sub folder with the datetime of the move



    Code:
    Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 5
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    --	select * from ledger_folder
    
    	Delete From Ledger_Folder_Parsed
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 6
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    	Insert Into Ledger_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 	  Ledger_Folder
    	 Where  Substring(dir_output,1,1) <> ' '
    	   And (Substring(dir_output,1,1) <> ' ' 
    	   And  Substring(dir_output,25,5) <> '<DIR>')
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 7
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    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.

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks Brett that certainly points me in the right direction...I'll give it a go.

Posting Permissions

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