Save txt file name using a naming convention...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Save txt file name using a naming convention...

Author

Topic

mitchel
Starting Member

11 Posts

Posted - 2005-08-12 : 14:32:35

Hi,

I finally got the DTP package (SQL Server 2000) up and running:

- it runs every hour (pulls records by looking at todays date and matching record submit dates)
- saves it as a txt file to the local server
- ftp's it to a remote server

I just found out that the txt file has to be unique and follow a certain
format:

MIOsampleYYYYMMDDHHMM

MIOsample = always the same
YYYY = Year 2005
MM = Month 08
DD = Day 08
HH = 24-hour clock (military)
MM = minutes

I saw a mention of using "Dynamic Properties Task" but have no idea how to use it or were to put it.

Any help would be greatly appreciated.

Thanks,

Mitch

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-12 : 14:45:12

Use an ActiveX script task to either:
a. initialise a local variable with the full date string (using date and datatype conversion functions) and set the data source property of the text file connection directly
or
b. initialise a global variable with this date string and use a dynamic properties task to set the data source property (might be slightly preferable if you're not comfortable with the DTS object model.

You'll need to set precedence constraints to ensure that these tasks run successfully before your datapump and ftp tasks.
Let me know if you need some more pointers.

Mark

mitchel
Starting Member

11 Posts

Posted - 2005-08-12 : 14:48:24

Mark,

Thanks for the help, but I should have mentioned that I am a super newbie in DTS and what you wrote makes sense but I have no idea were to begin.

Any detailed help would be GREATLY appeciated.

Thanks,

Mitch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-12 : 14:48:45

Here's how to do it in T-SQL:

SELECT 'MIOsample' + SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(varchar(50), GETDATE(), 120), '-', ''), ' ', ''), ':', ''), 1, 12)

You can then use DOS commands via xp_cmdshell to rename the file via an Execute SQL task.

Tara

mitchel
Starting Member

11 Posts

Posted - 2005-08-12 : 15:59:28

Tara,

Your response is most appeciated but clearly indicates to me that I have a lot to learn seeing that I am not sure were to place the T-SQL or how to execute the taks.

Could you please, if you have a moment describe in detail how to do it?

Thanks,

Mitch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-12 : 16:01:47

You place it in the Execute SQL Task in the DTS package. It allows you to run T-SQL commands.

Tara

mitchel
Starting Member

11 Posts

Posted - 2005-08-12 : 16:09:30

I created an Execute SQL Task and placed the SQL Code you provided, but do I need to connect this task to one of the other events? Right now it is just sitting there and when I execute the package I get an error on the execute ftp task.

Also, in the T-SQL code you provided me, does "MIOsample" already have to exist as a text file? How would it know which file to rename or create?

Please bare with me as I try to learn all of this.

Thanks,

Mitch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-12 : 16:18:25

The code that I provided just produces the file name. Nothing else. You'd use that string when you do the rename of your file. The task should execute after whatever makes sense. So your DTS package would save your data to a text file whose name is hard coded in the DTS package. You'd then use DOS commands to rename this file to the dynamic file name. To execute DOS commands from within T-SQL, you can use xp_cmdshell.

I am providing the T-SQL answer to your question which may or may not be the best solution. Another solution would be to write VBScript in an ActiveX task. But you'd have to know VBScript programming language.

I'm not sure what area of expertise you have, so it's hard to offer the right solution. There are several ways to skin the cat.

Tara

mitchel
Starting Member

11 Posts

Posted - 2005-08-12 : 18:10:07

OK...I got a lot further.

I added this to a Active X script and I got it work but the last hurdle is I cannot figure out how to add the HH (24-hour format) and the MM (minutes).

Any help would be greatly appreciated.

Mitch

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

' This script renames a file and adds a datestamp to it
' You could skip the whole datestamp thing and just rename the file too.

Dim FileDate
Dim DateString
Dim FSO
Dim File

FileDate = Date
DateString = DatePart("yyyy",FileDate) & Right("0" & _
DatePart("m",FileDate), 2) & Right("0" & DatePart("d",FileDate), 2)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.GetFile("c:\temp\MIOSample.txt")
File.Name = "newname" & DateString & ".txt"

Main = DTSTaskExecResult_Success
End Function

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-13 : 08:41:14

Hi Mitch
You can use the Hour() and Minute() vbscript functions. However, you'll have to initialise your FileDate variable with the Now() function as opposed to the Date() function, as the latter does not include time information.

Mark

mitchel
Starting Member

11 Posts

Posted - 2005-08-13 : 09:05:15

Mark,

The problem with Hour() is that it is not returning a double digit for hours:

exmaple:

9AM is coming back as 9 instead of 09

I've been trying to use the hour formatting (4) but I have yet to figure it out, I keep getting an error.

Any advice would be appreciated.

Thanks,

Mitch

-----------------------

Be nice and good things will happen to you.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-15 : 04:54:12

Hi Mitch
Just manipulate the string to add a leading zero if required as you've done for the other date elements.

Mark

mitchel
Starting Member

11 Posts

Posted - 2005-08-15 : 13:19:04

Thanks everyone, this was what I ended up with an worked:

DateString = DatePart("yyyy",FileDate) & Right("0" & DatePart("m",FileDate), 2) & Right("0" & DatePart("d",FileDate), 2) & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now) , 2)

-----------------------

Be nice and good things will happen to you.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-15 : 13:53:35

Glad to hear it!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources