DTS date stamp

Question

DTS date stamp

Kris-155042

Hall of Fame

Points: 3460
More actions
July 6, 2004 at 5:00 pm

#60199

Hi,
I'm fairly new to DTS packages. I currently have a DTS package that runs a T-SQL script and dumps a flat file into a directory on the network. However I can't seem to figure out how to date stamp the file so when it runs the following day it doesn't overwrite the previous file, I don't want it to append the file, I need a fresh dump everyday.
Can anyone please help?
Thanks
Thanks,
Kris

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Hi Kris,

I am assuming you are using a "Text File (Destination)" connection object right? If this is the case you need to dynamically change the name of the file to which you are outputting.

You do this by putting an ActiveX Task prior to the step that outputs to your text file. Put the following code into it:

Function Main()

Dim dt, dtstr

dt = Now

dtstr = CStr( DatePart( "yyyy", dt ) ) & _

Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _

Right( "0" & CStr( DatePart( "d", dt ) ), 2 )

'msgbox dtstr

DTSGlobalVariables.Parent.Connections("MyFile").DataSource = "C:\Myfile" & dtstr & ".txt"

'msgbox DTSGlobalVariables.Parent.Connections("MyFile").DataSource

Main = DTSTaskExecResult_Success

End Function

The code will change the name of the file to c:\MyFile<YYYYMMDD>.txt

I'm sure you can work out how to adapt this to give it the exact name that you want.

There are of course "many ways to skin a crocodile". When you get more familiar with DTS you may want to pass in a timestamp value from the app that calls the DTS package (probably via DTSRun) and then use a Dynamic Properties Task to set your file name. Don't run before you can walk though!

Hope this helps!

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

I don't want to sound like a twit, but I've never dealt with ActiveX in my life.

I've changed the "MyFile" to the actual directory it's going to but it's still failing. Is there something else I should be changing?

... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220485 (800403FB) Error string: Error Code: 0 Error Source= Microsoft VBScript compilation error Error Description: Syntax error Error on Line 8 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220485 (800403FB); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft VBScript compilation error Error Description: Syntax error Error on Line 8 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 51 Rows have been transformed or copied.; ... Process Exit Code 1. The step failed.

Thanks in advance

Thanks,

Kris

philcart SSC-Forever Points: 47794 More actions · Answer 3

Well you not alone about sounding like a twit, there's many of us of there. It's real easy to sound like a twit when you're dealing with DTS

You need to change the "MyFile" bit to the name of the connection, not the physical filename.

Eg: DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource = "C:\Myfile" & dtstr & ".txt"

Also, can you post what you changed the line to? Maybe you forgot a quote or something.

--------------------
Colt 45 - the original point and click interface

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

It does the export of the data but doesn't seem to put the time stamp on. I'm all confused

Function Main()

Dim dt, dtstr

dt = Now

dtstr = CStr( DatePart( "yyyy", dt ) ) & _

Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _

Right( "0" & CStr( DatePart( "d", dt ) ), 2 )

'msgbox dtstr

DTSGlobalVariables.Parent.Connections("\\sun\Kris\I52_Invoice").DataSource = "\\sun\Kris\I52_Invoice" & dtstr & '.txt'

'msgbox DTSGlobalVariables.Parent.Connections("\\sun\Kris\I52_Invoice").DataSource

Main = DTSTaskExecResult_Success

End Function

Thanks,

Kris

Jamie Thomson SSChampion Points: 11805 More actions · Answer 5

Hi Kris,

A few tips.

1. When you look at your DTS package in the package designer your connection object will have a name which is displayed with it. Lets call that <conn_name>

You need to change the line:

DTSGlobalVariables.Parent.Connections("\\sun\Kris\I52_Invoice").DataSource = "\\sun\Kris\I52_Invoice" & dtstr & '.txt'

to:

DTSGlobalVariables.Parent.Connections("<conn_name>").DataSource = "\\sun\Kris\I52_Invoice" & dtstr & '.txt'

2. In order to help you debug it remove the apostrophe from the beginning of:

'msgbox DTSGlobalVariables.Parent.Connections("\\sun\Kris\I52_Invoice").DataSource

(remembering to change \\sun\Kris\152_Invoice to <conn_name&gt

Apostrophes are VBScript's comments. When you run it you will get a standard windows popup telling you where it is going to output the file to.

3. You could also remove the apostrophe from the start of:

'msgbox dtstr

to check you are building the timestamp correctly.

4. In the package designer right-click on your ActiveX task and click "Execute Step". This will, unsurpisingly, just run that step. If it works successfully you will be able to look inside your connection object and see that the filename has indeed changed to whatever you set it to.

Hope this helps.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

How did you get on Kris?

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 7

I got rid of the apostrophes, but I'm a little confused at the connection name, Where do I find the connection name? I have a "Microsoft OLE DB Provider for SQL" and a "Text File (Destination)".

What am I not looking at properly? I feel like such a goose.

Thanks heaps in advance.

Thanks,

Kris

Jamie Thomson SSChampion Points: 11805 More actions · Answer 8

Kris,

The name of your connection is simply "Text File (Destination)".

So, the code you want is:

DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource = "\\sun\Kris\I52_Invoice" & dtstr & '.txt'

If I were you I'd change the name of that connection to something more readable. e.g. "OutputFile".

Don't worry about lack of understanding. We'll get you there in the end :o)

Regards

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 9

Thanks sooo much for your help (and patiance :-))

I have changed it to the following and it says there is an error on line 7:

Function Main()

Dim dt, dtstr

dt = Now

dtstr = CStr( DatePart( "yyyy", dt ) ) & _

Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _

Right( "0" & CStr( DatePart( "d", dt ) ), 2 )

msgbox dtstr

DTSGlobalVariables.Parent.Connections("Test File (Destination)").DataSource = "\\sun\kris\I52_Invoice" & dtstr & '.txt'

msgbox DTSGlobalVariables.Parent.Connections("Test File (Destination)").DataSource

Main = DTSTaskExecResult_Success

End Function

What else do I need to change?

Thanks,

Kris

Jamie Thomson SSChampion Points: 11805 More actions · Answer 10

Kris,

It is hard to look at the code and decide which is line 7 (strange but true).

Try commenting out (using an apostrophe) each line one at a time until it doesn't fail. Then you'll know which line is causing the error.

Let me know when you find out. At the moment I can't tell by looking at the code where the problem is.

Regards

P.S. Remember that:

dtstr = CStr( DatePart( "yyyy", dt ) ) & _

Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _

Right( "0" & CStr( DatePart( "d", dt ) ), 2 )

is a single line of code so make sure you comment all of it out!!!

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 11

O.K, the only line that seem so have a problem is:

DTSGlobalVariables.Parent.Connections("Test File (Destination)").DataSource = "\\sun\kris\I52_Invoice" & dtstr & '.txt'

Everything else seems to run fine.

Again, thanks heaps

Thanks,

Kris

Jamie Thomson SSChampion Points: 11805 More actions · Answer 12

I imagine the eror you are getting is something like "Unknown connection". Am I right?

Try "Text File (Destination)" instead of "Test File (Destination)"

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 13

Gees I felt like a goose then, but I've changed it and it still doesn't work, this is the exact error message:

Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : Error Code: 0

Error Source=Microsfot VBScript compliation error

Error Description: Syntax error

Error on Line 7

Thanks,

Kris

Jamie Thomson SSChampion Points: 11805 More actions · Answer 14

GODDAMIT. I've just seen where the problem is - and its entirely my fault, my sincerest apologies.

Try the following:

Function Main()

Dim dt, dtstr

dt = Now

dtstr = CStr( DatePart( "yyyy", dt ) ) & _

Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _

Right( "0" & CStr( DatePart( "d", dt ) ), 2 )

msgbox dtstr

DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource = "\\sun\kris\I52_Invoice" + dtstr + ".txt"

msgbox DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource

Main = DTSTaskExecResult_Success

End Function

The .txt was previously surrounded by apostrophes whereas it should be speech marks.

Sorry about that. This will definitely work now!

Regards

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson