Importing From Incrementally Named Text File

[C4rtm4N]

Hi,

I'm having a problem importing data from a text file that changes its name on a daily basis (the extension increments each day from 001 to 002, etc but the filename itself stays the same). Currently we have to rename the file each day so that DTS can make a connection to the file

Is there any easy way that I can set DTS to always look for filename.* ?

Thanks

Steve

 

[Catadmin]

I have something similar set up at my work place for tracking Transaction Log backup files, but it's going to take a little programming.

We have a table that contains Logname, Processed, Added, Deleted, ErrNum. Logname and Processed get updated with the TransactionLog backup file names and a 0 (for not processed) on VB code that is scheduled to run every 30 minutes. Added is a non-null field whose default is GetDate().

I have a T-SQL Script written to restore the logs that checks the Table to see if the logname has a 0 processed status or a 1 processed status. If 0, the script continues and restores all Logs with a 0 status, changing the 0 to a 1 when the script is through with that one restore.

You would have to set up some sort of VB (or other) script job to populate your table, then use a T-SQL script to populate your text file name which can then call the DTS package from within the script. Here's my Restore Log script. You'll have to re-work it to do what you need.

use Master

-- This code restores all logs that have been copied to C:\inout on the HealthOne server to the Transaction Central Database
-- and marks them as Processed (sets 0 to 1 - or True) in the TC Database, TransLog_Status table.


Declare @Logname char(50),
        @Errnum int,
        @Countloop int,
	@spid as varchar(10),
	@CMD as varchar(1000);


-- The following Cursor kills all connections on the Transaction Central database.

declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = 
        (select dbid from sysdatabases where name = 'TransactionCentral')
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
    set @CMD = 'kill ' + @spid
    exec (@CMD)
    fetch next from cur into @spid
END
close cur
deallocate cur

-- The following one line command changes the database to Single User so the restore can complete properly.

Exec sp_dboption @dbname = 'TransactionCentral', @optname = 'Single user', @optvalue = 'True';

Set @Errnum = 0;
Set @countloop = (Select count(logname) from tc.dbo.translog_status where processed = 0);

While @countloop <> 0
Begin
  Set @logname = (Select min(logname) from tc.dbo.translog_status where processed = 0);

  

  Restore Log TransactionCentral
  from disk=@logname
  with Standby = 'd:\inout\undos\undo.dat';

  Set @errnum = @@Error;
 
  

  If @errnum = 0 
  Begin Update tc.dbo.Translog_status
         Set processed = 1
         where Logname = @logname
  End 
  Else
        Break;

Set @countloop = @countloop - 1;

END;

-- The following one line command sets the database back to Multi-User mode so people can reconnect and do their work.

Exec sp_dboption @dbname = 'TransactionCentral', @optname = 'single user', @optvalue = 'False';

-- This code records an error number in the TC Database, TransLog_Status table for the Transaction Log backup where the 
-- restore failed.  This error code can be used in trouble shooting.

If @errnum <> 0
Begin 
  Use MSDB;
  Exec sp_update_job @job_name = Restore_TC, @enabled = 0 ;
  Use TC;
  Update tc.dbo.Translog_status
  Set Errnum = @errnum
  Where Logname = @logname
END;

I hope that helps you some.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[JayKusch]

There is a quick and dirty way to do it too.

In the folder where the file is to reside, create a batch file called something like RenameIt.bat

In there add the code ..

ren c:\SameFileName.* *.NeededFileExtension

Of course put the correct path in for C:\ and change the SameFileName to your static file name and last but not least, change the NeededFileExtension to the extension the file needs to be. This code just rename the extension of your current file w/ the extension of the needed file.

Now as you first step in your DTS package, create an "Execute Process Task" from your Task selections. Open it up and put the path and name of the batch file we created in the "Win32 Process" dialog box. You can also use the browse function to drill down and select it.

That should do the trick in renaming it. If you need to make this an automated process, you could add another Execute Process Task to maybe rename it back to its original extension, move it to an archive folder for safe keeping and then delete it from your working folder where the rename operation takes place. And BINGO ... your ready for the next run.

Thanks

J. Kusch

 

[C4rtm4N]

Thanks guys

On some days we have multiple text files so I'd previously discounted the batch file idea despite it being much simpler. After seeing the responses (& the fact that Catadmin's seemed a bit too complicated for me) I revisited things & am merging the text files using 'type' in DOS then renaming them to the same filename each day.

It's working just fine now calling the cmd file from the DTS package

Thanks

Steve