Name the file to include the date:time run 1

[Eyespi20]

I have a DTS package that creates a tab delimited file and I want to name the results with the date and time appended to the name every time it's run so I know when the file is new(I know all I have to do is look at the properties, but this is a client request and you all know how they are).

Can someone give me a means of doing this within the DTS?

I'm really green when it comes to this kind of thing, so all your help is greatly appreciated.

Margaret

 

[dbomrrsm]

Really depends on how you are creating the file from DTS - tell us more of how the file is created and I am sure we can help.

[blue]DBomrrsm[/blue]

 

[Eyespi20]

The DTS runs a query on the database and spits the resultset out in a tab delimited file located on another drive.

Margaret

 

[dbomrrsm]

You could declare a variable and set it to getdate() and then use this variable in your code where you name the file.

[blue]DBomrrsm[/blue]

 

[Eyespi20]

Unfortunately, the DTS doesn't seem to support naming the file using this method. You name it outside of the script as far as I can tell.

Thanks for the suggestion, though.

Margaret

 

[dbomrrsm]

are you naming the file useing code - if so can you paste the code ?

[blue]DBomrrsm[/blue]

 

[Eyespi20]

No, you can't name the file with code. You have to name it in a box where you put your path to the location you are saving it to.

I did write code in the job and in Query analyzer, it works, but does not when I use the @filename as the name of the file. This makes the whole job fail.

declare @dttm as varchar(16)
declare @filename as varchar(30)

set @dttm = cast(getdate()as varchar(16))
set @filename = 'TTCData ' + @dttm

In the path to the file I put \\servername\foldername\@filename and the job failed.

Thanks

Margaret

 

[dbomrrsm]

Margaret

can you use the @path variable ?

declare @dttm as varchar(25)
declare @filename as varchar(30)
declare @path varchar (100)

set @dttm = cast(getdate()as varchar(25))
set @filename = 'TTCData ' + @dttm
set @path = '\\servername\foldername\'+@filename

print @path

[blue]DBomrrsm[/blue]

 

[Eyespi20]

Well, it put a file in the correct place but it's named @filename, not the result of the variable execution.

It might as well be a Monday.

Margaret

 

[dbomrrsm]

can you show me the code that executes the creation of the file and named it @filename

[blue]DBomrrsm[/blue]

 

[Eyespi20]

declare @cr char(2)
SET @CR=Char(13)+Char(10)
declare @cr2 char(2)
set @cr2 = Char(13)
declare @dttm varchar(16)
set @dttm = cast(getdate() as varchar(16))
declare @filename varchar(30)

declare @path varchar (100)


set @filename = 'TTCData ' + @dttm + '.txt'

set @path = '\\Ttc-appserver1\Channelinx\Outgoing\'+@filename



select distinct
	dbo.vw_OpenCalls.[Request ID],
	dbo.vw_OpenCalls.[Bill To],
	dbo.vw_OpenCalls.[Site ID],
	dbo.vw_OpenCalls.[Site],
	dbo.vw_OpenCalls.[Store Number],
	dbo.vw_OpenCalls.[Site Address],
	dbo.vw_OpenCalls.[Site City],
	dbo.vw_OpenCalls.[Site State],
	dbo.vw_OpenCalls.[Site Phone],
	dbo.vw_OpenCalls.[Tech ID],
	dbo.vw_OpenCalls.[Tech],
	dbo.vw_OpenCalls.[Tech Address],
	dbo.vw_OpenCalls.[Tech City],
	dbo.vw_OpenCalls.[Tech State],
	dbo.vw_OpenCalls.[Tech Phone],
	dbo.vw_OpenCalls.[Tech Fax],
	Problem = replace(replace(dbo.vw_OpenCalls.Problem,@cr, '') ,@cr2,''),
	dbo.vw_OpenCalls.[Call Status],
	dbo.request_text.text AS [Maximum Charge]
from
	dbo.vw_OpenCalls
left outer join
	dbo.request_text on dbo.vw_OpenCalls.[request id] = dbo.request_text.request_id
where 
	dbo.request_text.text_line_code = 'mxcg'


print @path

 

[dbomrrsm]

what sort of a task do you use to execute this code in the DTS package is it an execute SQL task and if so what is the destination (I guess a text file destination) but just checking.

[blue]DBomrrsm[/blue]

 

[Eyespi20]

It's a simple export to tab delimited text file task.

Thanks

Margaret

 

[dbomrrsm]

dont think you can do it as simply as that - I have a few pieces of code that you could use (I think) but it would involve you creating the first proc then the second and the second would have to create your query as dynamic sql for it to run.

Create procedure s_SavePackages
@Path	varchar(128)
as
/*

*/

	set nocount on

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @FileName varchar(128)
declare	@FilePath varchar(128)
declare	@cmd varchar(2000)
	
	select 	@ServerName = @@ServerName ,
		@FilePath = @Path
	
	if right(@Path,1) <> '\'
	begin
		select @Path = @Path + '\'
	end
	
	-- create output directory - will fail if already exists but ...
	select	@cmd = 'mkdir ' + @FilePath
	exec master..xp_cmdshell @cmd
	
	
create table #packages (PackageName varchar(128))
	insert 	#packages
		(PackageName)
	select 	distinct name
	from	msdb..sysdtspackages
	
	select	@PackageName = ''
	while @PackageName < (select max(PackageName) from #packages)
	begin
		select	@PackageName = min(PackageName) from #packages where PackageName > @PackageName

		select	@FileName = @FilePath + @PackageName + '.dts'

		exec @rc = sp_OACreate 'DTS.Package', @objPackage output
		if @rc <> 0
		begin
			raiserror('failed to create package rc = %d', 16, -1, @rc)
			return
		end

		exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
			@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
		if @rc <> 0
		begin
			raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
			return
		end
		
		-- delete old file
		select @cmd = 'del ' + @FileName
		exec master..xp_cmdshell @cmd, no_output
		
		exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
		if @rc <> 0
		begin
			raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
			return
		end
		
		exec @rc = sp_OADestroy @objPackage
	end

GO

Then

CREATE   PROCEDURE BACK_UP_DTS
AS 
DECLARE @strSQL nvarchar (500)
SET @strSQL = 'exec master.dbo.s_SavePackages '+'''M:\'+(SELECT CONVERT(VARCHAR(10), GETDATE(), 102))+''''
--PRINT @strSQL
EXEC sp_executesql @strSQL
EXEC NETSEND_WJ

GO

Hope this helps !

[blue]DBomrrsm[/blue]

 

[Eyespi20]

Thanks, DBomrrsm. I'll give it a try and let you know.

Margaret