DTS Package: How can I save the source file with a new name? 1

[Kenny100]

Hi Folks

I've just successfully setup a DTS Package that takes data from a CSV file and imports it into a table in my database. This is scheduled to run each day at noon.

My problem is that I only want the package to run when it finds a file with a particular name (data.csv) and, if it does, import the data, save the original file with a timestamp (e.g. data-2005-03-24.csv) and delete the original file.

Can all of this be done with DTS? Do I need to do some VBScript?

Cheers,
Fiddler.

 

[yksvaan]

.. be done with DTS?" With DTS itself, you can't actually do much else than transform data and have a package workflow. And even this can be encapsulated in a vb program, since dts is a programming object model.

But within DTS package you can use different tools like sql (against different data source dialects) and vbscript.

You can write the renaming into a sql procedure or into a activex (vb)script task within the package workflow. The trick is how you gonna check file existens, or actually how you gonna control the workflow. The simplest way is to let the transform task just fail, which leads to package fail status, but if this is okay, it's okay.

Renaming in a sql procedurecan be done with the help of master..xp_cmdshell, see BOL.

One way is (outside of the package) to have an agent job and control with step succession.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[JohnDTampaBay]

You could do it with two scripts. Create this one and have it run before the just before import task. Set the import task to run on success of this task. It will check for file existence. If the file exists, it will copy the file and succeed. If the file doesn't exist, it will fail.

'  Copy File

Function Main()

  DIM oFSO
  SET oFSO = CreateObject("Scripting.FileSystemObject")

  StringDate = CStr(Year(Now))

  IF Len(CStr(Month(Now))) = 1 THEN
      StringDate = StringDate + "-0" + CStr(Month(Now)) 
  ELSE
      StringDate = StringDate + "-" + CStr(Month(Now))
  END IF

  IF Len(CStr(Day(Now))) = 1 THEN
      StringDate = StringDate + "-0" + CStr(Day(Now)) 
  ELSE
      StringDate = StringDate + "-" + CStr(Day(Now))
  END IF

'-- MsgBox StringDate

  NewFile = "Data-" + StringDate + ".CSV"

  IF oFSO.FileExists("Data.CSV") THEN
      oFSO.CopyFile "Data.CSV", NewFile

      Main = DTSTaskExecResult_Success
  ELSE
      Main = DTSTaskExecResult_Failure
  END IF

  End Function

Create this script and set it to run on success of the import task. It will delete the file.

'  Delete file

Function Main()

  DIM oFSO
  SET oFSO = CreateObject("Scripting.FileSystemObject")

  oFSO.DeleteFile("Data.CSV")

  Main = DTSTaskExecResult_Success

End Function

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Kenny100]

Thanks guys and thanks especially to you John. Your solution worked perfectly ... absolutely flipping PERFECTLY!

Cheers!